Check Old Database for Page Verification Method – Torn Page versus Checksum

I always keep an eye out for used gear that I can pick up at a good price. Muskie fishing is an expensive sport, and having top of the line, brand new gear isn’t always necessary. I watch Craigslist and keep my eyes open at rummage sales to try to find lures, rods/reels, nets, and anything else that may be able to help me catch fish. One important thing to remember whenever buying used gear is to tune it up. If they are lures, sharpen the hooks and make sure they run straight. If it’s a reel, lube the applicable parts.
One important routine in SQL Server is DBCC CheckDB. This function will do a consistency check on the database by checking the each page for inconsistencies. Starting in SQL Server 2005 the database engine can use a CHECKSUM function to make this check. Once you enable CHECKSUM on a database (enabled by default in SQL Server 2005 and above), every time a page is written to disk a checksum is calculated over the contents of the whole page and is placed in the page header. When a page is read from the disk, and when DBCC CheckDB is run, the CHECKSUM will be recalculated and compared to the value stored in the page header. If the value isn’t the same as on the page header, you’ve got an indication of data corruption, most likely caused by the storage disk.

This is not good.

This is not good.

There are a few things to keep in mind regarding this consistency check. First, databases created in SQL Server 2000 will be using Torn-page for verification, instead of CHECKSUM. After upgrading a database to a new compatability level above SQL Server 2000, you have to manually go into the database settings and change the verfication type to CHECKSUM. This is important to do as CHECKSUM is far better at detecting inconsistencies than Torn Page. Next, the CHECKSUM will be added to pages when writing them to disk. This means existing data that is not written will not have the CHECKSUM in the page header, and DBCC CHECKDB will not use CHECKSUM to verify data integrity. A good way to get the CHECKSUM into the page header is to rebuild the table (clustered index or heap) and all the nonclustered indexes. Lastly, DBCC CHECKDB will only check pages that are allocated. This means if there is a problem with a page that is not being used, you will not see an error. This can be confusing if you are running DBCC CHECKDB overnight, then re-indexing afterward as the bad page may no longer be used by the time you are able to inspect the system. This doesn’t mean the I/O system fixed itself – you are still having problems that need to be looked at.

USE [master]
GO
ALTER DATABASE [DB_Test] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT
GO

The takeaway from this is to check each database that may have been created in SQL Server 2000 or earlier to make sure CHECKSUM page verification is being used, and to make sure you are running DBCC CHECKDB on a regular basis.

Much better!

Much better!

Advertisements