Database maintenance involves different tasks. Because most of these tasks have been discussed in previous chapters, this section does not go into detail on tasks already covered. Instead, there is a checklist that you can use as a starting point for your maintenance efforts. The rest of the section explains how to set up maintenance plans and run database consistency checks.
The following checklists provide recommended daily, weekly, and monthly maintenance tasks.
Daily | |
|---|---|
✓ | Monitor application, server, and agent logs. |
✓ | Configure alerts for important errors that are not configured for alert notification. |
✓ | Check for performance and error alert messages. |
✓ | Monitor job status, particularly jobs that back up databases and perform replication. |
✓ | Review the output from jobs in the job history or output file, or both. |
✓ | Back up databases and logs (as necessary, and if not configured as automatic jobs). |
Weekly | |
|---|---|
✓ | Monitor available disk space on drives. |
✓ | Monitor the status of linked, remote, master, and target servers. |
✓ | Check the maintenance plan reports and history to determine the status of maintenance plan operations. |
Generate an updated record of configuration information by executing sp_configure. |
Monthly | |
|---|---|
✓ | Monitor server performance, tweaking performance parameters to improve response time. |
✓ | Manage logins and server roles. |
✓ | Audit server, database, and object permissions to ensure that only authorized users have access. |
✓ | Review alert, job, and operator configurations. |
As Needed | |
|---|---|
✓ | Back up the SQL Server Registry data. |
✓ | Update the Emergency Repair Disk. |
✓ | Run database integrity checks and update database statistics. (SQL Server 2005 handles this automatically in most cases.) |
Maintenance plans provide an automated way to perform essential maintenance tasks. You can run a maintenance plan against a single database or multiple databases running on a designated target server. You can also generate report histories for maintenance plan execution.
You create maintenance plans with the Maintenance Plan Wizard or with the Maintenance Plan Package Designer. Both techniques are similar:
With the wizard, the wizard pages guide you through the steps of choosing servers, selecting maintenance tasks to perform, configuring execution history logging, and setting an execution schedule. When you complete the wizard steps, the wizard generates the package that will perform the designated maintenance tasks.
With the package designer, you specify servers, add tasks to perform from a pre-defined list of maintenance tasks, and configure execution history logging as necessary. After you configure connections to the server on which you want to perform maintenance, you build the maintenance plan by dragging tasks from the Maintenance Tasks toolbox to the design window. The order in which you add tasks sets the order of execution. If a task requires additional input, such as database or server names, double-clicking the task opens a properties dialog box that lets you specify the needed information.
The set of maintenance tasks you can perform is similar whether you are working with the wizard or the designer. These tasks are:
Back Up Database. Allows you to specify the source databases, destination files or tapes, and overwrite options for a full, differential or transaction log backup. In the wizard interface, there are separate task listings for each backup type.
Check Database Integrity. Performs internal consistency checks of the data and index pages with the designated databases.
Execute SQL Server Agent Job. Allows you to select SQL Server Agent jobs to run as part of the maintenance plan.
Execute T-SQL Statement. Allows you to run any T-SQL script as part of the maintenance plan. (Only available in the Maintenance Plan Package Designer.)
Cleanup History. Deletes historical data about Backup and Restore, SQL Server Agent, and Maintenance Plan operations.
Maintenance Cleanup. Deletes files created when executing maintenance plans. (Only available in the Maintenance Plan Package Designer.)
Notify Operator. Sends an e-mail message to a designated SQL Server agent operator. (Only available in the Maintenance Plan Package Designer.)
Rebuild Index. Rebuilds indexes to improve the performance of index scans and seeks. This task also optimizes the distribution of data and free space on the index pages, allowing for faster future growth.
Reorganize Index. Defragments and compacts clustered and nonclustered indexes on tables and views to improve index-scanning performance.
Shrink Database. Reduces the disk space used by the designated databases by removing empty data and log pages.
Update Statistics. Updates the query optimizer statistics regarding the distribution of data values in the tables. This improves the query optimizer’s ability to determine data access strategies, which can ultimately improve query performance.
When you first start working with maintenance plans, you will probably want to run the Maintenance Plan Wizard and let the wizard design the necessary package for you. After you have created a package, you modify it in Maintenance Plan Package Designer view.
Tip
For most installations, I recommend configuring separate maintenance plans for system and user databases. This gives you greater flexibility when determining how and when maintenance operations are performed. For large installations, you may want to have separate maintenance plans for each database so that you can work with different databases on different days or at different times of the day.
You can create a maintenance plan by completing the following steps:
In SQL Server Management Studio, access the Management folder on the server where you want to create the maintenance plan. This can be a different server from the one on which the maintenance plan will run.
Right-click Maintenance Plans, and then select Maintenance Plan Wizard. This starts the Maintenance Plan Wizard.
Read the welcome dialog box, and then click Next. Type a name and description for the maintenance plan, such as Engineering DB Server Maintenance and Backup Plan.
Use the Server list box to specify the server on which you want to perform the maintenance tasks. The currently selected server is listed by default. To select a different server, click the Properties (...) button and select an available SQL Server.
Choose the authentication mode for connecting to the server specified in step 4, and then click Next. If you choose Use Windows Authentication, the login account and password from the server’s connection setting are used to determine the authentication method for connecting to the target server. If you choose Use SQL Server Authentication, you can specify the user name and password for a SQL Server login.
As shown in Figure 15-16, select the maintenance tasks you want to perform, and then click Next. The order in which tasks are listed on the Select Maintenance Task Order page determines the order in which the tasks are executed. Select a task and then click the Move Up or Move Down button as appropriate to change the task’s order. When the tasks are in the desired run order, click Next.
Next, for each task that can be applied to one or more databases, you will need to choose the database(s) on which the tasks will be performed. Typically, you can perform a task on all databases, all system databases, all user databases, or a combination of one or more individual databases. You may also need to configure individual task parameters. When you are finished configuring tasks, click Next. Following are guidelines for each task:
Back Up Database. Select the databases for which you want to create a full, differential, or transaction log backup. You can back up to disk or tape, and either append or overwrite existing backup files. Typically, you will want to create a backup file for every selected database. With disk-based backups, you can set a specific backup directory and create subdirectories for each database being backed up. You can also set the file extension for the backups. The default extension is .bak. To verify the integrity of backups upon completion, select Verify Backup Integrity.
Check Database Integrity. Select the databases on which you want to perform internal consistency checks. By default, both data and index pages are checked. If you only want to check data pages, clear Include Indexes.
Execute SQL Server Agent Job. Select SQL Server Agent jobs to run as part of the maintenance plan. Any available jobs on the server are listed, and you can select the related check box to execute the job whenever the maintenance plan runs.
Cleanup History. Historical data about backup and restore, SQL Server Agent, and maintenance plan operations is stored in the msdb database. When the history cleanup task runs, any historical data older than four weeks is deleted on the target server by default. You can modify the type of historical data cleaned up and set the Older Than criteria to different values. For example, you might find that you need to maintain historical data for a full quarter. If so, you would set Remove Historical Data Older Than to 3 Months.
Rebuild Index. Select the databases on which you want to rebuild indexes. If you select specific databases, you can specify whether all table and view indexes are rebuilt or only a specific table or view index is rebuilt. For example, if you want to rebuild the NWCustomer view in the Orders database, you would click in the Databases drop-down list, select These Databases, choose the Orders database, and then click OK. Next, under Object, select View, and then click in the Selection drop-down list, select These Objects, choose dbo.NWCustomers, and click OK. The affected indexes are dropped and re-created with a new fill factor. You can chose Reorganize Pages With The Default Amount Of Free Space to recreate indexes with the original fill factor that was specified when the indexes were created. Or you can choose Change Free Space Per Page Percentage To if you want to specify a new fill factor. The higher the percentage, the more free space is reserved on the index pages and the larger the index grows. The default is 10 percent. Valid values are from 0 to 100.
Note
Fill factors are discussed in Chapter 6, in the subsection titled "Setting the Index Fill." Reorganizing pages changes table indexes and thus invalidates existing statistics. You cannot reorganize data and update statistics in the same plan, and you may want to create separate maintenance plans for handling each of these important tasks.
Reorganize Index. Select the databases you want to defragment and compact. If you select specific databases, you can specify whether all tables and views are reorganized or only a specific table or view is reorganized. For example, if you want to reorganize the Customer table in the Orders database, you would click in the Databases drop-down list, select These Databases, choose the Orders database, and then click OK. Next, under Object, select Table, and then click in the Selection drop-down list, select These Objects, choose dbo.Customers, and click OK.
Shrink Database. Select the databases on which you want to reduce the disk space by removing empty data and log pages. Use Shrink Database When It Grows Beyond to specify the database size that triggers this task. Free space in a database is removed only when the size of the database file exceeds this value. The default value is 50 MB, which means that if there is more than 50 MB of free space, SQL will shrink the database to the size specified. Use Amount Of Free Space To Remain After Shrink to set the amount of unused space that should remain after the database is reduced in size. The value is based on the percentage of the actual data in the database. The default value is 10 percent. Valid values are from 0 through 100. Free space can be returned to the operating system or retained for future use by the database.
Update Statistics. Select the databases on which you want to update query optimizer statistics. If you select specific databases, you can specify whether statistics for all tables and views are updated or only a specific table or view is updated. By default, both column and index statistics are updated. You also can specify to only update column or index statistics.
When you are finished configuring tasks, you will see the Select Plan Properties page shown in Figure 15-17. By default, maintenance plans are configured to run manually when you run them (on demand). You can schedule the maintenance plan to run automatically by defining a run schedule. Click Change, and then set a schedule for the maintenance operations you have selected. Click OK to set the schedule, and then click Next.
Use the Select Report Options page, shown in Figure 15-18, to determine how maintenance plan reports are handled. By default, whenever a maintenance plan runs, a report is generated. The report can be written to a file in any designated folder location, can be sent by e-mail to a SQL Server Agent operator, or both. Click Next.
Review the maintenance plan. Click Finish to complete the process and generate the SQL Server Agent job to handle the designated maintenance tasks. These jobs are labeled according to the name of the maintenance plan. Click Close.
Creating a maintenance plan is only the beginning. After you create the plan, you will need to check the maintenance reports and history periodically. Maintenance reports are stored as text files in a designated directory, are sent as e-mail to designated SQL Server Agent operators, or both. You can view file-based reports in a standard text editor or word processor. To access the maintenance history through SQL Server Management Studio, complete the following steps:
In SQL Server Management Studio, access the Management folder on the server of your choice.
Right-click Maintenance Plans, and then select View History. This displays the Log File Viewer as shown in Figure 15-19.
Under Select Logs in the left pane, Maintenance Plans should be selected by default. There should also be a log entry for each maintenance plan configured on the server.
Choose the maintenance plan(s) for which you want to review a job history.
Use the summary in the right pane to review the job history. Click Close when you are finished.
You can view, edit, run, or delete maintenance plans by completing the following steps:
In SQL Server Management Studio, access the Management folder on the server of your choice.
Select Maintenance Plans in the left pane. You will see the existing maintenance plans in the right pane.
You can now:
View or edit a maintenance plan by double-clicking the maintenance plan entry in the right pane. This opens the plan in the Maintenance Plan Package Designer.
Delete a maintenance plan by selecting its entry and pressing Delete. In the Delete Object dialog box, click OK to confirm the deletion.
Execute the maintenance plan by right-clicking it and selecting Execute.
You rarely have to perform database integrity checks with SQL Server 2005, and when you do, you can use maintenance plans to handle most of the work. On those rare occasions when you want to perform consistency checks manually, you will use the DBCC command. DBCC stands for database consistency check. There are many different DBCC commands, and the ones you will use most often to maintain a database are covered in the following sections.
The DBCC CHECKDB command checks the consistency of the entire database and is the primary method used to check for database corruption. The command ensures that:
Index and data pages are linked correctly.
Indexes are up to date and sorted properly.
Pointers are consistent.
The data on each page is up to date.
Page offsets are up to date.
Example 15-1 shows the syntax and usage for the DBCC CHECKDB command. When you run the command without a repair option, errors are reported but not corrected. To correct errors, you need to put the database in single-user mode, and then set a repair option. After you repair the database, create a backup.
Example 15-1. DBCC CHECKDB Syntax and Usage
Syntax
DBCC CHECKDB
( 'database_name' | database_id | 0
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ] )
[ WITH {
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
[ , [ DATA_PURITY ] ] }
]Usage
DBCC CHECKDB ('customer', NOINDEX)
DBCC CHECKDB ('customer', REPAIR_REBUILD)The REPAIR_FAST option performs minor repairs that do not consume a lot of time and will not result in data loss. The REPAIR_REBUILD option performs comprehensive error checking and correction that requires more time to complete but does not result in data loss (but the database must be in single-user mode). The REPAIR_ALLOW_DATA_LOSS option performs all the actions of REPAIR_REBUILD and adds new tasks that may result in data loss. These tasks include allocating and deallocating rows to correct structural problems and page errors as well as deleting corrupt text objects.
Tip
When trying to fix database problems, start with REPAIR_FAST or REPAIR_REBUILD. If these options do not resolve the problem, use REPAIR_ALLOW_DATA_LOSS. Remember that running the REPAIR_ALLOW_DATA_LOSS option may result in unacceptable loss of important data. To ensure that you can recover the database in its original state, place the DBCC command in a transaction so that you can inspect the results and roll back the transaction, if necessary.
To correct problems with individual tables, you can use the DBCC CHECKTABLE command. As shown in Example 15-2, the syntax and usage for this command are almost the same as for DBCC CHECKDB. The database you want to work with must be selected for use.
Example 15-2. DBCC CHECKTABLE Syntax and Usage
Syntax
DBCC CHECKTABLE
( 'table_name' | 'view_name'
[ , NOINDEX
| index_id
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD }
] )
[ WITH
{ [ ALL_ERRORMSGS | NO_INFOMSGS ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ] }
]Usage
DBCC CHECKTABLE ('receipts')
DBCC CHECKTABLE ('receipts', REPAIR_REBUILD)To check the consistency of database pages, you can use DBCC CHECKALLOC. Again, the syntax for this command is nearly identical to the previous DBCC commands. One item worth noting is that although Example 15-3 shows a NOINDEX option, it is maintained only for backward compatibility with previous SQL Server versions. The command always checks the consistency of page indexes.
Example 15-3. DBCC CHECKALLOC Syntax and Usage
Syntax
DBCC CHECKALLOC
( [ 'database_name' | database_id | 0 ]
[ , NOINDEX
|
{ REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ] )
[ WITH { [ ALL_ERRORMSGS ]
[ , NO_INFOMSGS ]
[ , TABLOCK ]
[ , ESTIMATEONLY ] }
]Usage
DBCC CHECKALLOC ('customer')
DBCC CHECKALLOC ('customer', REPAIR_REBUILD)Another useful DBCC command is CHECKCATALOG. You use this command to check the consistency of a database’s systems tables. Example 15-4 shows the syntax and usage of this command.
To rebuild one or more indexes on a database, you can use DBCC DBREINDEX. Example 15-5 shows the syntax and usage of this command.