Understanding DBCC (Database Console Commands) is a step toward becoming a better and well-rounded DBA for Workforce Central. In my last post, I covered the basics of DBCC and two of its commands: SQLPERF and HELP. In this post, I will cover the DBCC CHECKALLOC command.
The DBCC CHECKALLOC command checks the consistency of disk space allocation structures for a database. What does this really mean? Why do we care? How do the disk space allocation structures get into an inconsistent state in the first place?
When SQL Server writes data to the physical storage, it does so by "asking" the operating system to write the data. However, SQL Server does not have any way of knowing that data was written correctly until the next time that SQL Server reads the data from storage. The operating system might have driver, hardware, firmware, or hard drive issues that corrupt the data as is written to the disk. At the same time, the hard disk does not know that the write was successful; the hard disk doesn't verify the writes to the disk.
Because the execution of DBCC CHECKALLOC can take a long time (sometimes hours), SQL Server doesn't perform a consistency check of the disk space allocation structures when SQL Server is started. This would slow the starting of SQL Server. Additionally, when you have lots of RAM compared to the size of the database, very few data reads are done from the disk after the data is paged into RAM. This means that SQL Server has very few opportunities to check the consistency of the data write to the storage system. Therefore, it is up you to run the following statement on your databases periodically:
DBCC CHECKALLOC
Ideally, you want to perform DBCC CHECKALLOC before you do a full backup of the database. Otherwise, SQL Server may back up a corrupt version of the database, which would be worthless in a restore operation.
When SQL Server encounters a consistency error before DBCC CHECKALLOC is called, by reading corrupted data from the hard disk, SQL Server logs errors to the error log. See Richard's posts for more details about the SQL Server error log.
One thing to note: When you run the DBCC CHECKALLOC command without any modifiers, it takes a snapshot of the database and checks the snapshot's consistency. This allows a production database to keep processing transactions as DBCC CHECKALLOC works. This requires additional space on the storage system. If you are running low on storage space, you can avoid performing the snapshot. For more information, see the DBCC CHECKALLOC topic in SQL Server Books Online.
DBCC CHECKALLOC returns lots of information about the disk space application structures. I tend to go to the bottom of the output and scan for this line:
CHECKALLOC found 0 allocation errors and 0 consistency errors in database 'Kulshan'.
After I have found this line, I know there are no issues and everything is fine.
Don't despair if you have corruption in your disk space allocation structures. I will be writing about ways to fix the issues and how to do other kinds of data integrity checks by using DBCC.
Wayne Berry (6230289B-5BEE-409e-932A-2F01FA407A92) is a computer programmer and serial entrepreneur with a passion for dynamic Web sites that run on SQL Server. When he is not blogging for MSDN, you can find him blogging for Project 31-A. http://www.31a2ba2a-b718-11dc-8314-0800200c9a66.com