Wednesday, January 23, 2013

Importance of DBCC CHECKDB Command

Quite often, it has been seen that some of the DBAs do not run DBCC CHECKDB command on his/her database. I don't understand why DBAs not perform this on their database regularly. It is very simple to run, and it saves you from big trouble later on.

What is DBCC CHECKDB Command?
This command checks logical & physical integrity of all the objects in the desired database. It includes all three command checks (DBCC CHECKALLOC, DBCC CHECKTABLE & DBCC CHECLCATALOG) in itself. If DBA has run DBCC CHECKDB command on the database then he/she should not three command on the database as DBCC CHECKDB includes all these commands. If corruption has occurred in the database due to some reason, DBCC CHECKDB command will check it and report you about which object have corrupted. It has three optional arguments that help you to fix the reported corruption, and the arguments are
  1. Repair_Rebuild: When you use this argument with DBCC CHECKDB, there is no possibility of data loss. It will try to repair & rebuild corrupt database without data loss.
  2. Repair_Fast: It does not perform any repair operation on the specified database but maintains the syntax for backward compatibility.
  3. Repair_Allow_Data_Loss: As name suggests, it will try to repair corrupt database with minimum data loss.
Note: Before running above repair arguments, make sure your database is in single-user mode. If database is in multi-user mode then ALTER it, and set it in single-user mode.

How do you use DBCC CHECKDB?
You can use it on your database by performing T-SQL

DBCC CHECKDB AdventureWorks
Replace AdventureWorks with the name of your database.

What is the best time interval to Run DBCC CHECKDB?
It depends upon your database importance, use, and so many other factors also. Running DBCC CHECKDB on the database regularly will not avoid the corruption to happen but it will help in determining the corruption as soon as it happens.

How to check last DBCC CHECKDB?
If you want to know, when had the last DBCC CHECKDB command performed on the database then run bellow command