Lesson 3: Maintaining SQL Server

[Previous] [Next]

To maintain your database, you should perform several tasks, either manually or automatically. These tasks form the core of a database maintenance plan. They include

• Updating information about data optimization
• Verifying the integrity of data
• Performing backups
• Creating reports and a maintenance history of the database

If you choose to have these tasks performed automatically, you can set up and schedule your database maintenance plan with the Database Maintenance Plan Wizard.

This lesson gives details on each task you should consider for your database maintenance plan and takes you through the process of automating your plan.

After this lesson, you will be able to

• Develop a database maintenance plan
• Use the Database Maintenance Plan Wizard to implement a database maintenance plan

Estimated lesson time: 40 minutes

Developing a Database Maintenance Plan

Several tasks can help you maintain your database. The most important tasks, which should be performed for all SQL Server databases, are updating data optimization information, verifying data integrity, performing backups, and keeping a history of maintenance activities. These tasks should be performed on a regular basis. How often you run these tasks depends on the level of database activity and the size of your database. As you learned in Chapter 13, you can create scheduled SQL Server jobs to perform these tasks automatically.

Update Information About Data Optimization

As data and index pages fill up, updating requires more time, and pages can become fragmented. Reorganizing your data and index pages can improve performance.

Maintain Indexes Using the fillfactor Option

You can specify the percentage of available free space (fillfactor) in your index and data pages. This enhances performance: if space is available in existing pages when performing inserts and updates, SQL Server does not have to split pages and allocate new pages. The fillfactor percentage is used when the index is first created and whenever the index is rebuilt. You can specify a percentage or allow SQL Server to select the optimal value automatically.

Update Statistics That Are Used by the Query Optimizer

You should run UPDATE STATISTICS on tables that are being modified. This updates the information about the key value distribution for one or more indexes in a table, which the query optimizer uses to generate optimal query plans.

Remove Unused Space from the Database Files

You can execute DBCC SHRINKDATABASE to recover any unused disk space in the database tables. You can also enable the autoshrink option on databases.

Verify the Integrity of Data

Data integrity tests detect inconsistency in the database caused by hardware or software errors.

Perform Internal Data Integrity Tests

Execute DBCC CHECKALLOC to check the allocation of data and index pages for each table within the extent structures of the database.

Perform Database Integrity Tests

Execute DBCC CHECKDB to check the allocation and structural integrity of the objects in the database. Execute DBCC CHECKTABLE to check the integrity of the data, index, text, and index pages for a table. If DBCC finds an error, you can specify that it repair the error automatically.

DBCC CHECKDB performs all of the checks performed by DBCC CHECKALLOC and DBCC CHECKTABLE on each table in the database. If you run DBCC CHECKDB regularly, it is not necessary to run DBCC CHECKALLOC and DBCC CHECKTABLE as well. If time is limited, you can use DBCC CHECKALLOC and DBCC CHECKTABLE to perform smaller checks at different times rather than running a full DBCC CHECKDB at once. If DBCC CHECKDB reports only allocation errors, you can use DBCC CHECKALLOC to repair the errors. The safest option is to run DBCC CHECKDB with the repair option; this repairs all errors, including allocation errors. While DBCC CHECKDB is running, it is not possible to create, alter, or drop tables.

NOTE

---

Previous versions of SQL Server had a DBCC NEWALLOC statement. This is supported in SQL Server 7 for backward compatibility only; you should use DBCC CHECKALLOC instead.

Perform Backups

Perform backups on a regular basis to protect against data loss. You should back up the transaction log to capture changes to the database between full database backups.

Maintain a Maintenance History

Maintain a history of the maintenance tasks. This history should include what actions were performed, as well as the results of any corrective actions.

Automating the Database Maintenance Plan Tasks

You can use either the Database Maintenance Plan Wizard or the sqlmaint utility to automate your database maintenance plan so that it runs on a regularly scheduled basis.

The Database Maintenance Plan Wizard

The Database Maintenance Plan Wizard helps you set up the core maintenance tasks that are necessary to ensure that your database performs well, is regularly backed up in the event of a system failure, and is checked for inconsistencies. When you run the wizard, you will specify the following:

Databases That the Plan Maintains

You can define a single maintenance plan for all databases or plans for one or more databases.

Data Optimization Information

You can have the wizard reorganize data and index pages, update the index statistics to ensure that the query optimizer has current information regarding the spread of data in the tables, and compress data files by removing empty database pages.

Data Verification Tests

You can have the wizard perform internal consistency checks of the data and data pages within the database to ensure that a system or rare software problem has not damaged data. You can specify whether indexes should be included in the checks and whether the wizard should attempt to repair minor problems that it finds.

Frequency and Destination of Backups

You can schedule database and transaction log backups and keep backup files for a specified time.

Location of History Files

The results that the maintenance tasks generate can be written as a report to a text file, saved in history tables, or sent in an e-mail message to an operator.

The sqlmaint Utility

Use the sqlmaint utility to execute DBCC statements, dump a database and transaction logs, update statistics, and rebuild indexes. The sqlmaint utility is a command-prompt utility that performs functions similar to those handled by the Database Maintenance Plan Wizard.

Exercise: Using the Database Maintenance Plan Wizard to Create a Database Maintenance Plan

In this exercise, you will create a maintenance plan using the Database Maintenance Plan Wizard.

• To create a database maintenance plan that consists of multiple jobs by using the Database Maintenance Plan Wizard

1. Switch to SQL Server Enterprise Manager.
2. On the Tools menu, click Wizards.
3. Expand Management, click Database Maintenance Plan Wizard, and then click OK to run the wizard. Click Next on the first screen of the wizard.
4. Click These Databases, and then check only StudyNwind in the Databases list. Click Next.
5. Check the Update Statistics Used By Query Optimizer option. Set the value of the Sample option to 10% of the database.
6. Check the Remove Unused Space From Database Files option. Set the value of the When It Grows Beyond option to 5 MB. Set the value of the Amount Of Free Space To Remain After Shrink option to 15% of free space.
7. Click Change to open the Edit Recurring Job Schedule dialog box. In Occurs, click Monthly. In Monthly, set the value to Day 1 Of Every 3 Month(s).
8. Click OK to close the Edit Recurring Job Schedule dialog box. Click Next.
9. Check the Check Database Integrity option.
10. Click Change to open the Edit Recurring Job Schedule dialog box. In Occurs, click Monthly. In Monthly, set the value to The 1st Saturday Of Every 1 Month(s).
11. Click OK to close the Edit Recurring Job Schedule dialog box. Click Next.
12. Check the Back Up The Database As Part Of The Database Plan option.
13. Click Change to open the Edit Recurring Job Schedule dialog box. In Occurs, click Weekly. In Weekly, set the value to Every 1 Week(s) On Sunday. In Daily Frequency, set the value to Occurs Once At 11:00 PM.
14. Click OK to close the Edit Recurring Job Schedule dialog box. Click Next.
15. Check and set the Remove Files Older Than 2 Week(s) option.
16. Click Next.
17. Check the option Backup The Transaction Log As Part Of The Maintenance Plan.
18. Click Change to open the Edit Recurring Job Schedule dialog box. In Occurs, click Weekly. In Weekly, set the value to Every 1 Week(s) On Monday, Wednesday And Friday. In Daily Frequency, set the value to Occurs Once At 11:00 PM.
19. Click OK to close the Edit Recurring Job Schedule dialog box. Click Next.
20. Check and set the option Remove Files Older Than 1 Week(s).
21. Click Next.
22. Check the option Write Report To A Text File In Directory.
23. Click Next.
24. Check Write History To The msdb.dbo.sysdbmaintplan_history Table On The Local Server.
25. Check the option Limit Rows In The Table To. Set the value to 1000 rows for this plan.
26. Click Next.
27. In Plan Name, type StudyNwind Maintenance Plan.
28. Click Finish to create the new plan. Click OK to close the confirmation that your maintenance plan has been created.
29. In the console tree, expand Management, then click the Database Maintenance Plans icon.
30. In the details pane, right-click StudyNwind Maintenance Plan, and then click Properties. Review your new plan, noting that the wizard has created a plan with all of the settings that you selected.
31. Click Cancel to close the Database Maintenance Plan dialog box. If you are prompted to save changes, click No.
32. In the console tree, expand SQL Server Agent, and then click the Jobs icon. Verify that four jobs were created successfully for StudyNwind Maintenance Plan.
33. In the details pane, right-click Integrity Checks Job For DB Maintenance Plan 'StudyNwind Maintenance Plan', and click Start to manually start the Integrity Check Job.
34. Review the job history to verify that the job was executed successfully.

Lesson Summary

It is important to regularly perform various maintenance tasks on your databases. Regular maintenance includes keeping table and index statistics up to date, performing database consistency checks, making backups, and organizing database space allocation. You can automate maintenance by creating your own jobs or by creating a database maintenance plan using the Database Maintenance Plan Wizard.