- Backups
- Consistency Checks
- Index Maintenance
- Updating Statistics
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Consistency Checks
If you've worked with SQL Server for a while, you know at least a few database consistency checker (DBCC) statements. There are dozens of documented and undocumented DBCC commands, with various functions. Earlier versions of SQL Server required running table and database consistency checkers on a regular basis to ensure that all data and index pages were appropriately linked and that none of the pages were damaged. You had to run DBCC CHECKDB prior to each full backup to ensure that you weren't backing up a damaged database. With SQL Server 2000, you're no longer required to run DBCC CHECKDB or DBCC CHECKALLOC statements. The latest version of SQL Server automatically detects damaged pages and fixes them. Ninety-nine percent of database corruption with SQL Server 2000 happens due to hardware failures.
Even with the above facts in mind, however, it never hurts to be cautious. It's still a good idea to run DBCC CHECKDB to ensure the overall health of your database prior to backing it up. In SQL Server 2000, DBCC CHECKDB uses schema locks, instead of the shared table locks used in the previous versions. Therefore, the new version of the statement provides a higher level of concurrency than the previous version. As with backups, DBCC CHECKDB is a highly intensive operation and should be performed during off-peak hours.
Other DBCC commands that you might find useful for managing and tuning your database are DBCC SHRINKFILE and DBCC SHRINKDATABASE. Both of these commands are used to reduce the database file size—smaller database files are easier to manage. When SQL Server allocates space for tables, it usually leaves some room for growth. As tables grow larger, SQL Server keeps allocating additional space. Unless you have the AUTOSHRINK option turned on, SQL Server doesn't automatically release all of the space when data is deleted. Therefore, you might want to occasionally shrink the database files to make sure that the database is as compact as possible.
CAUTION
You might think that you should keep the AUTOSHRINK option turned on—please don't try this in a production environment! The AUTOSHRINK option slows down database performance significantly, since SQL Server has to continuously monitor the disk space usage and free up the space every time data is deleted. You're much better off turning off this option and shrinking database files manually as needed.