DBCC CHECKDB is vital for SQL Server databases to be 100% sure that there is no corruption. However, due to databases growing massive in size, its very hard to find a maintenance window when you claim to be 24x7 up. Over the years, SQL Server team has implemented various mechanisms that will detect most common forms of corruptions especially related to Physical corruption caused by hardware.
SQL Server 2005 and up has PAGE_VERIFY = CHECKSUM which can help you proactively detect physical corruption in database pages thereby adding a checksum to each page as it is written to the I/O system and validates the checksum as it is read from the disk.
Also, backup (full or differential) with CHECKSUM will guarantee to detect any I/O corruption caused by hardware.
Hence, from the hardware side of corruption, SQL Server does a good job of detecting it and reporting it. (Make sure to set important corruption related Alerts as well).
That being said, still logical corruption, scribbler induced errors - where in-memory pages are corrupted either by third-party code running inside the SQL Server process or by drivers or other software with sufficient privileges executing in Windows kernel mode and/or SQL Server Bugs, etc are undetectable using above methods and hence CHECKDB comes into picture.
DBCC CHECKDB performs a more thorough checks that includes checking page headers for possible corruption that are not detectable by any other means.
Any existing scripts out there?
Instead of reinventing the wheel, I would highly recommend you to take a look at Ola's SQL Server Integrity Check solution
Efficiently running DBCC CHECKDB :
You just need to be creative when you are tight in maintenance window having huge databases or high number of databases to run CHECKDB on.
After attending SQLSkills training, what I have implemented in my environment is :
- prioritize on what tables are critical to check.
- separate the tables into groups with different priorities and then run
DBCC CHECKTABLE
along with running DBCC CHECKALLOC
and DBCC CHECKCATALOG
- Create a worker table that will store the table names with priorities. Just make sure that all high prioritie tables (which are massively big) are not in one group else your CHECKDB will not complete at all.
- You can even have a timeout column in your worker table that will orchestrate when your CHECKDB will get killed once it has passed the maintenance window
- Add how long it took per table to run
DBCC CHECKTABLE
, DBCC CHECKALLOC
and DBCC CHECKCATALOG
. So that you can get a feel on how long it is usually taking for your checks to run.
- You can even run with
NOINDEX
option as it will speed up the operation as it does not check the Non-Clustered Indexes on user tables. This has some advantage as it is not that critical as Data corruption since no data is lost and you can drop and recreate the Index if necessary.
Obviously, Enterprise edition can take advantage of Parallel execution of DBCC statements, but look out for MAXDOP setting as it might end up taking all your CPU. This can be hard limited by Resource Governor.
Note: If you are having SPARSE column, then your CHECKDB will be dead slow as described here.
Finally, its how to prevent database corruption by utilizing all available tool set + your faith in your database server hardware system and most importantly the value of your data.
Some excellent references :