In moments of distress, the database consistency check (DBCC) may prove to be your most valuable friend. This tip provides a thumbnail sketch of the things DBCC can do for you, which include the following:
- Check the integrity of your tables and their related indexes.
- Check the whole database.
- Check the integrity of database pages.
- Rebuild the indexes on any given table.
Why you need to befriend DBCC
If you are wondering why using DBCC is even necessary, here are the reasons:
- Database pages (both tables and indexes) need to be split from time to time, which can result in bad allocations.
- Indexes can become corrupted or simply inefficient.
- The SQL Server engine can occasionally misunderstand your intentions.
- In situations where a large number of updates is the norm, things can get hairy (remember that any given update is actually a delete plus an insert).
- Individual pages, while still "sound," may lose their optimal storage footprint.
How to run DBCC
You can run DBCC in two ways: from a command window and from inside a Query Analyzer window. You can also schedule its operations if you deem it necessary. (I have never felt the need to do this because, of all Microsoft's products, I am most confident in the stability of SQL Server. I believe that it is the finest product ever to emerge from Redmond. But, things can still go wrong.)
The DBCC command has the following extensions:
- CheckDB: checks the consistency of the entire database, and is the basic method to check for database corruption.
- CheckTable: checks a specified table for problems.
- CheckAlloc: checks the individual pages allocated to a database, both tables and indexes.
- Reindex: rebuilds the indexes on a specified table.
- CacheStats: tells you about the objects currently stored in the memory cache.
- DropCleanBuffers: drops all the data currently stored in the buffer, so that you can continue testing without using the previous results.
- Errorlog: erases (truncates) the current log. You might consider scheduling a job consisting of this command to run once a week or so.
- FlushProcInDB: clears out the stored procedure cache for the specified database (use its dbid not its name). Discover its id using:
SELECT dbid FROM master.dbo.sysdatabases
WHERE name = '<name your poison>
- IndexDefrag: reduces fragmentation in indexes without imposing a lock on the files so that users can continue working with the database.
- CheckCatalog: checks the specified database for consistency in tables and between tables (the latter means foreign keys etc.).
How to use five of these extensions
DBCC first creates a snapshot of your database (except in certain special circumstances, such as working with a Master, TempDB, or read-only database). A proviso: In order to use DBCC, your database must be in single-user mode.
Using DBCC CheckDB
This command ensures that:
- Data and index pages are correctly linked.
- Indexes are sorted correctly and are up to date.
- Pointers are consistent.
- Data on each page is up to date.
- Page offsets are up to date.
Here are three of the most common ways to use CheckDB:
DBCC CHECKDB ('AdventureWorks', REPAIR_FAST)
DBCC CHECKDB ('AdventureWorks', REPAIR_REBUILD)
DBCC CHECKDB ('AdventureWorks', REPAIR_ALLOW_DATA_LOSS)
There are several other options that you may specify, but these are the three critical options. I have presented the DBCC commands in the order in which you should run them and then check the results afterwards. The first two options will not result in a loss of data, while the third will cause data loss. It's advisable to place the third command inside a transaction so you can perform a ROLLBACK if the data loss is unacceptable.
Using DBCC CheckTable
Any problems you encounter will most often be with one or more tables within a database rather than the entire database. In these cases, run DBCC CheckTable. First, use the database of interest and then run the DBCC CheckTable command. Here are two examples:
DBCC CheckTable ('Sales,SalesOrderHeader')
DBCC CheckTable ('Sales,SalesOrderHeader', REPAIR_REBUILD)
Using DBCC CheckAlloc
This command checks the consistency of data pages and their indexes. Here are two examples:
DBCC CHECKALLOC ('Sales.SalesOrderDetails')
DBCC CHECKALLOC ('Sales.SalesOrderDetails', REPAIR_REBUILD)
Using DBCC CheckCatalog
Use this command to verify the consistency of a database's system tables. You specify the name of the database to check and optionally the argument WITH NO_INFOMSGS. Here is an example:
DBCC CHECKCATALOG ('AdventureWorks')
Using DBCC ReIndex
This command forces the reconstruction of one or more indexes on a given table or view. You can also supply the name of a particular index, as well as a fill factor. Listing A contains two examples. The third argument specifies that I want a fill factor of 90% on the recreated index.
Now that you know the most common uses of DBCC, you can learn about additional arguments and options for each command variation by consulting Books Online.
Miss a tip?
Check out the SQL Server archive, and catch up on the most recent editions of Arthur Fuller's column.
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!