Fundamentals: DBCC - SQL Server's FDISK

[SQL-Server-Team]

First published on TECHNET on May 20, 2011

[Prior Post In Series] [Backup Series] [Next Post in Series]

In a time long long ago (last millennium), DBCC was known as Database Consistency Checker; today it stands for Database Console Command. It performs many functions similar to what the old utility FDISK did on hard drives -- checks for errors of various types and optionally fixes them. The many different commands available are listed in the documentation . Many functions have been moved out of DBCC into friendlier commands. The essential DBCC command to be familiar with is CHECKDB.

• DBCC CHECKDB - Checks the consistency of logical and physical data written on drives.
  
  
  
  • Used after encountering hardware problems.
  
  
  • If errors are reported, it may indicate pending failures of hardware.
  
  
  • Consists of various processes that may be called individually.
  
  
  
  

DBCC CHECKDB

Like FDISK, you can ask for an analysis or for repair (if a problem is found).

• When running an analysis:
  
  
  
  • the database may be in use
  
  
  • you may break the analysis command without any harm
  
  
  
  


• When running a repair:
  
  
  
  • the database should not be in use and should be in single user mode
  
  
  • you should not break the command
  
  
  
  

One word of warning about breaking: DBCC executes using parallelism (multiple cores), if you break it, any rollback is single threaded (single core).

This command incorporates the following sub-commands.

• DBCC CHECKALLOC - Checks the consistency of disk-space allocation-structures for a specified database.


• DBCC CHECKFILEGROUP - Checks the allocation and structural integrity of all tables and indexed views in the specified filegroup of the current database.


• DBCC CHECKTABLE - Checks the integrity of all the pages and structures that make up the table or indexed view.


• DBCC CHECKCATALOG - Checks for catalog consistency within the specified database. The database must be online.

Checking a Database

Before you run DBCC CHECKDB on your production system, run it on your test system with a copy of the production system (to get the same volume of data). If DBCC CHECKDB runs for 7 days and it is executing against your production system, you may have some unhappy users. DBCC CHECKDB can be CPU intensive.

CheckDB uses lots of resources:

• Memory


• CPU


• IO Bandwidth


• TempDB

My usual recommendation (for simple cases) is to do

DBCC CHECKDB {DBNAME} WITH NO_INFOMSGS

This results in nothing but "Command(s) completed successfully" usually. There is no need to read through dozens of information messages. This command may take seconds,  hours or days, depending on the size of your database and resources available.

With a long running query, it is nice to know how far along you are. The status may be obtained by executing in another query window the command below

SELECT Command, Percent_Complete FROM sys.dm_exec_requests

The output is show below.

If the time to execute DBCC CHECKDB is excessive, fear not -- in a future post, I will build an infrastructure and automated code to decompose the steps and execute each step in a time window to minimize the impact on production.

Repairing a Database

If DBCC CHECKDB reports a problem, things can become messy.  The best strategy is to restore the database; do not attempt a repair by yourself.  I recall doing an FDISK repair on a hard drive and finding eight new generic folders contains thousands of 'recovered files'. The physical drive was fixed; but data on it was useless without a lot of work. The same situation can happen with DBCC repairs. The database physical structure is fixed, but data in it may be corrupted and could require a massive manual effort to make usable. In reality, you may have lost your database.

With a physical hard drive containing valuable information, you would send it out to a specialist hard drive recovery shop because the information is worth the cost. With SQL Server,  contact Microsoft Support before attempting a repair. I quote directly from the documentation below (and Microsoft Support has shouted in my ear the same thing)

Important

Use the REPAIR options only as a last resort . To repair errors, we recommend restoring from a backup . Repair operations do not consider any of the constraints that may exist on or between tables. If the specified table is involved in one or more constraints, we recommend running DBCC CHECKCONSTRAINTS after a repair operation. If you must use REPAIR, run DBCC CHECKDB without a repair option to find the repair level to use. If you use the REPAIR_ALLOW_DATA_LOSS level, we recommend that you back up the database before you run DBCC CHECKDB with this option.

The good news is that 99+% of the time, you can restore the database from backups and transactions logs -- if properly done. You do not need to do a repair (or contact Microsoft Support).

Restoring a Database

I covered details of restoring from full backups, differential backups and transaction logs in another series of posts . Below I give a checklist to assist you in sequential order :

1. [SQL/OS] Take the database out of production.


2. [SQL] Do a COPY-ONLY backup of the corrupted database. This is an insurance policy and will likely not be used.


3. [OS] Copy all of the backups and transaction logs for the production server to a test server.


4. [SQL] Restore the database, using the backups and transaction logs on the test server .


5. [SQL] Execute DBCC CHECDB on the test server. It must be without any errors .


6. [SQL] Do a COPY-ONLY backup of the restored database on the test server.


7. [OS] Copy the above backup to the production server from the test server.


8. [SQL] Install the COPY-ONLY backup on the production server.


9. [SQL] Execute DBCC CHECDB on the production server. It must be without any errors .


10. [Application] Do appropriate testing of  the restored database on the test server .
    
    
    1. Any changes will not be seen on the production server.
    
    


11. [SQL/OS] Put the production server back into production.

If there are problems following this sequence, do everyone a favor and contact Microsoft Support . The number of factors that need to be considered and explore are too many to explain in a blog or forum post.

Thou shall become hip!

DBCC has been around a long time and often things become deprecated (like thou hath) and modern ways are not acquired. The table below shows some of the deprecated DBCC commands and their modern equivalents. The deprecated commands are linked to the official documentation. If you are searching on the internet for information and find example code using any of these commands, ignore them and move on.

Deprecated CommandsEquivalent Process

DBCC SHOWCONTIG	sys.dm_db_index_physical_stats
DBCC DBREPAIR	DROP DATABASE
DBCC CONCURRENCYVIOLATION	obsolete (removed in SQL Server 2008)
DBCC UNPINTABLE	obsolete (removed in SQL Server 2005)
DBCC PINTABLE	obsolete (removed in SQL Server 2005)
DBCC NEWALLOC	DBCC CHECKALLOC obsolete (removed in SQL Server 2005)
DBCC ROWLOCK	obsolete (removed in SQL Server 2005)
DBCC TEXTALL	DBCC CHECKDB obsolete (removed in SQL Server 2005)
DBCC TEXTALLOC	DBCC CHECKTABLE obsolete (removed in SQL Server 2005)
DBCC DBREINDEX	ALTER INDEX REBUILD
DBCC INDEXDEFRAG	ALTER INDEX REORGANIZE

With special thanks to Paul Randal , SQLskills.com for correcting technical errors. Paul wrote DBCC CHECKDB and the rest of DBCC.