[Notes from Pinal]: There is always scenario when we suddenly end up having an error in our database query which is related to database integrity. We see error where SQL Queries are not executed due to there is corruption in the database file. When we see this happening, in many cases it is pretty late to correct the issue. It would be nice we have been checking the database integrity frequently and making sure that our database is in the best state to return us result anytime when we want. Tim tasks about database integrity in this episode of Notes from the Field.
Linchpin People are database coaches and wellness experts for a data driven world. In this 11th episode of the Notes from the Fields series database expert Tim Radney (partner at Linchpin People) explains a very common issue DBA and Developer faces when they are dealing with database integrity. Do not forget the most important aspect for any system – Database Integrity!
When I get asked to review a database server environment, a very important check that I perform is to review when the last DBCC CHECKDB scan was ran on each database. It is very important to check for integrity regularly on all production databases.
Scheduling DBCC CHECKDB to run on a regular basis is a really good practice. DBCC CHECKDB checks the logical and physical integrity of all the objects in the respective database in which it is being ran. I highly recommend reading a previous blog post that Pinal Dave wrote that gives a great introduction and explanation on DBCC CHECKDB.
As a consultant and full time DBA I perform these checks very often and have a set of detailed scripts that I use. The basics of obtaining when the last time DBCC CHECKDB was ran can be obtained from running the following script.
EXEC ('DBCC DBInfo() With TableResults, NO_INFOMSGS')
The output will resemble the following:
You will want to review the output from this script and look for “dbi_dbccLastKnownGood’ in the Field column. This isn’t too difficult to perform if you are just searching for a few databases but in my experience I have to run this against many instances with numerous databases on each instance. I would recommend building a script that will loop through each database, insert the output from the above result set into a temporary table and only report back where Field = ‘dbi_dbccLastKnownGood’. There are a number of these scripts already written and published on the web.
What is crucial is that you are performing regular DBCC CHECKDB scans. If you are not proactively checking for integrity problems you are putting yourself at risk of losing data. If data becomes corrupt and you do not have backups predating the corruption then you have in most cases lost data. I recommend even if you have a job scheduled to run regular checks to create a report to scan each server to validate they are being ran.
It is worth noting that DBCC CHECKDB is a very IO intensive operation and should be ran during maintenance windows or during nonpeak times. I know of several organizations that have a process in place to perform backup restore validations in a dedicated environment. They perform their integrity scans as part of that process enabling them to offload the integrity checks to a non-production environment.
However you architect your integrity checks, just make sure you are performing them and that your backup process protects you in the event corruption is found. If you are prepared then when corruption does happen, you will be ready.
If you want me to take a look at your server and its settings or if your server is facing any issue we can Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
