You never see the termites, you only see the damage they have done. And by then it is often too late.
Same with corruption inside of your database. Often time most folks have no idea there is any issue until it is too late.
Wouldn’t it be nice if there was a quick and easy way to determine if your database was corrupted?
Turns out there are some quick and easy tools for SQL Server but they are buried so deep inside you are likely to never even know they exist. Just like termites under your doorstep, or unicorn bacon.
These tools are the policies that ship with SQL Server, but are not installed by default. They are on your servers right now, doing nothing, except waiting for you to discover them. The one I am going to show you today is named ‘Database Page Status’. It relies on a couple of things. First, that your databases have the page verify set to CHECKSUM. Second, that a corruption has happened and an entry has been written to the suspect_pages table.
I made a video to show you how entries get written to the suspect_pages table, how they get removed (or how Microsoft believes they get removed), and how to find those hidden policies.
If you aren’t the type that enjoys using policies then you could use a different method such as Powershell in order to check all of your instances for corruption. The key is to build your process to check the msdb..suspect_pages table.
But you should do something. Before it is too late.
Nice, keep playing with SQL during your evening
I ran SELECT COUNT(*) FROM [msdb].[dbo].[suspect_pages] on my CMS and was reminded of that long and terrible thanksgiving weekend in 2009. Thanks Tom. On the bright side I’ve been corruption free since then.
Great post!
Nice example of leveraging Policy Based Management to identify suspect pages. Now only if Microsoft can give us that policy with a facet that would support On Change – Notify!