When you think about corruption, what’s the first thing that comes to your mind? Politicians or SQL Server databases?
Undoubtedly, most people will think about politicians first. But, as a DBA, you might want to think about corruption in your databases first.
In either case, corruption is always bad and can cause problems, such as a loss of money and/or jobs.
For example, a corrupt politician could steal or misappropriate taxpayer money that could directly lead to financial hardships within their government. Additionally, these financial difficulties could directly lead to budget cuts, which could cost some people their jobs.
And if you have corruption in your SQL Server databases, this could lead to similar problems. For instance, if you have a client’s database, and it gets corrupted and you can’t retrieve their data. You have pretty much lost a client and hopefully won’t get sued. And depending on the circumstances, that may cost someone their job.
How do you check for corruption in a SQL Server database?
You have to run DBCC CHECKDB (http://msdn.microsoft.com/en-us/library/ms176064.aspx), which will check the logical and physical integrity of all the objects in the specified database. Now, there are lots of options to that command and it is good idea to become familiarize with what it is doing. (NOTE: If you are using the maintenance plans within SQL Server, it is called the “Check Database Integrity Task”)
Now, running the command is just the start. You have to look at the output. This is what is going to tell you whether you have corruption and give you an idea of what you can do to fix it.
Additionally, it is a good idea to run this daily because you want to catch the corruption in your database as soon as possible. However, many environments can’t run this check with that frequency because it is IO intensive. So, at the very least, I would say weekly. Any less than that and it seems like you are just begging for trouble.
How can I determine when was the last time the corruption check was good?
The only way I know how to determine when was the last time the DBCC CHECKDB ran successfully is to use the undocumented command, DBCC DBINFO.
Here’s how I use it to get that information on every database on the server:
CREATE TABLE #DBCC_DBINFO (
ParentObject VARCHAR(255) ,
[Object] VARCHAR(255) ,
Field VARCHAR(255) ,
CREATE TABLE #Results (
ServerName VARCHAR(255) ,
DatabaseName VARCHAR(255) ,
@command1 = 'USE ? INSERT INTO #DBCC_DBINFO EXECUTE (''DBCC DBINFO WITH TABLERESULTS'')',
@command2 = 'INSERT INTO #Results SELECT @@SERVERNAME, ''?'', Value FROM #DBCC_DBINFO WHERE Field = ''dbi_dbccLastKnownGood''',
@command3 = 'TRUNCATE TABLE #DBCC_DBINFO'
-- BUG: duplicate record in SQL Server 2008 for dbi_dbccLastKnownGood
SELECT ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName, LastCleanDBCCDatetime ORDER BY LastCleanDBCCDatetime) AS RowID
DELETE FROM DBCC_CTE WHERE RowID > 1;
WHEN '1900-01-01 00:00:00.000' THEN 'Never ran DBCC CHECKDB'
ELSE CAST(LastCleanDBCCDatetime AS VARCHAR)
END AS LastCleanDBCCDatetime
WHERE DatabaseName <> 'tempdb'
ORDER BY LastCleanDBCCDatetime
DROP TABLE #DBCC_DBINFO
DROP TABLE #Results
What should I do if I have a corrupt database?
Now, I’m not claiming to be an expert on fixing database corruption. But, unfortunately, I have had to fix the corruption on quite a few databases during my career. At one company, before I arrived, they were not checking the integrity of the databases or backing up the databases on a few servers. So, when I first ran the command on the databases, I was shocked by the amount of databases that were reporting errors. And from this experience and my research, I learned a lot about fixing corrupt databases.
Here are the general steps I follow in order to fix database corruption:
- Restore from backup – Ideally, if you catch the corruption soon enough, restoring from a backup is the best way.
- Use DBCC CHECKDB repair commands – If you do not have a backup available, the output of the DBCC CHECKDB command will specify an option to use to repair the database. (Note: Using the option “REPAIR_ALLOW_DATA_LOSS”, will most likely cause you to lose data.)
- Contact someone who knows more about this (Microsoft or SQL MVP) – Paul Randall has an excellent blog (http://www.sqlskills.com/blogs/paul/category/corruption/) about corruption in SQL Server, which I feel is a must read!
Remember, corruption is always bad, and we want to fix it as soon as possible.