Both a Weekly and a Daily Database Maintenance Plan should be created and scheduled to run accordingly.
The Weekly Database Maintenance Plan, as outlined below, should be run initially before setting them both to run on a schedule.
Daily Database Maintenance
VersaDev recommends the following Daily Database Maintenance plan be created:
These recommended tasks should be added in the order below. VersaDev is not responsible for setting up Maintenance plans (unless negotiated otherwise)
1. Check database integrity
2. Check database size; in this step the business needs to consider defining an alert threshold to ensure that the database never reaches capacity and fails, the below query can be used to achieve this. As a recommendation the DBA should set a warning of at least 15% to ensure that the additional space is made available. - ***This option is not available via the wizard.
WITH CTE_DBSIZE AS (
SELECT [size] * 8 As [DBSize], [filename]
FROM sysfiles
)
SELECT CAST(SUM([DBSize] / 1024.0 / 1024.0) As decimal(5, 2)) As [DBSize (GB)]
FROM CTE_DBSIZE
3. Backup Database
4. Reorganise Index
5. Update Statistics
6. Clean-up History
7. Maintenance Clean-up
Weekly Database Maintenance
VersaDev recommends the following Weekly Database Maintenance plan be created:
These recommended tasks should be added in the order below. VersaDev is not responsible for setting up Maintenance plans (unless negotiated otherwise)
1. Check database integrity.
2. Check database size; in this step the business needs to consider defining an alert threshold to ensure that the database never reaches capacity and fails, the below query can be used to achieve this. As a recommendation the DBA should set a warning of at least 15% to ensure that the additional space is made available. - ***This option is not available via the wizard.
WITH CTE_DBSIZE AS (
SELECT [size] * 8 As [DBSize], [filename]
FROM sysfiles
)
SELECT CAST(SUM([DBSize] / 1024.0 / 1024.0) As decimal(5, 2)) As [DBSize (GB)]
FROM CTE_DBSIZE
3. Backup Database
4. Rebuild Index
5. Update Statistics
6. Clean-up History
7. Maintenance Clean-up