DBCC CHECKDB Not Run Recently
When SQL Server writes data to your drives, it just assumes everything’s okay until it needs to read the data back again.
Unfortunately, in the event of storage corruption, the storage isn’t so kind as to alert SQL Server. And sometimes, SQL Server has even been known to corrupt itself.
We need to periodically check to make sure the data on disk still makes sense, and that’s where DBCC CHECKDB comes in. It checks for both allocation errors and consistency errors. Additionally, if there are any issues, the error messages will come back with information regarding where the errors exist, the severity of the error as well as the suggested repair option.
It’s essential to run CHECKDB on a regular basis to find errors if they exist because if there’s corruption, we need to fix it before our last good backups disappear.
To find the problem, we check each database to make sure DBCC CHECKDB has completed a successful check in the last two weeks.
Don’t Believe It?
“But I run CHECKDB all the time,” you might say – well, let’s find out:
|
1 |
DBCC DBINFO('StackOverflow') WITH TABLERESULTS |
That’s it. But the output is a nightmare. It’s about 80 lines of stuff you will probably never care about. Around line 50 is what you’re looking for.
And this is probably what you’ll see! A date of 1900-01-01 etc. That means never. If you run DBCC CHECKDB on the database, perhaps like so:
|
1 |
DBCC CHECKDB('StackOverflow') WITH NO_INFOMSGS, ALL_ERRORMSGS |
And then re-run the DBCC DBINFO command, our date is now updated to current:
If the date isn’t updating, that means one of two things: SQL Server can’t write to the database (like it’s read-only), or it’s finding errors (hoowee, not good.)
To Fix the Problem
Decide whether you’d like to use built in Maintenance Plans, or a free tool like Ola Hallengren’s free CHECKDB scripts. A couple of considerations:
- Maintenance plans have a graphical interface, in case you don’t know T-SQL.
- Ola Hallengren’s free scripts take care of fine-tuned options, like automatically running data_purity checks (which is a good thing).
How to Set Up Regular Maintenance to Check for Corruption
Option 1: Use a Maintenance Plan to Run DBCC CHECKDB
If you’re using maintenance plans, you can edit maintenance plans in SQL Server Management Studio under Management, Maintenance Plans. Your server may have multiple maintenance plans that run CHECKDB for different types of databases, like one for system databases and one for user databases.
Expand the Management node in SSMS, right click on Maintenance Plans, and select New Plan.
From the Toolbox window, which sometimes gets hidden over to the left, drag the Check Database Integrity Task over to the Maintenance Plan tab.
When you double click on the Check Database Integrity Task box in the Maintenance Plan tab, you’ll be able to choose which databases you want to have checked during the process.
From there, all that’s left is scheduling it, which is just like scheduling an Agent Job.
After you save and close the maintenance plan, make sure to find the related job in the SQL Server Agent and set the Notification properties to let an operator know if it fails.
Option 2: Set up SQL Agent Jobs to Run DBCC CHECKDB Using Ola Hallengren’s Free Scripts
And you’re implementing integrity checks for the first time, consider using Ola Hallengren’s free maintenance scripts. They’re not quite as easy as maintenance plans, but they’re much more flexible. They also include the ability to break up DBCC operations into smaller chunks for large databases. On very large databases CHECKDB can take a significant amount of time and resources – the WITH PHYSICAL_ONLY option is faster, but omits the logical checks.
Once the Agent jobs are in place, you can schedule them to run as simply as Maintenance Plans, or you can fine tune them with the additional options documented here.