Does Your Database Have any Integrity?
Corruption. We know it is everywhere. It is surely a hot-button issue in the news. If you have not given much thought to database integrity, now is the time to sit up and pay attention. Corruption can occur at any time. Most of the time database corruption is caused by a hardware issue. No matter the reason, being proactive on database integrity will ensure your spot as a hero DBA in the face of corruption.
A single lie destroys a whole reputation of integrity – Baltasar Gracian
Integrity is one of those words people throw around quite often these days. The definition of integrity is the quality of being honest and having strong moral principles. How can data have strong moral principles? Does data integrity mean something different? Yes, data integrity refers to the accuracy and consistency of stored data.
Have Backups, Will Travel
When was the last time an integrity check was run on your database? If you are sitting there scratching your brain trying to find the answer to that question, you may have serious issues with your database and not know it.
But, I have solid backup plans in place, so this means I am okay. Right?
While having a solid backup and recovery plan in place is an absolute must, you may just have solid backups of corrupt data. Regular integrity checks will test the allocation and structural integrity of the objects in the database. This can test a single database, multiple databases (does not determine the consistency of one database to another), and even database indexes. Integrity checks are very important to the health of your database and can be automated. It is suggested to run the integrity check as often as your full backups are run.
As discussed in an earlier blog, Validating SQL Server Backups, your data validation needs to take place BEFORE the backups are taken. A best practice is to run a DBCC CHECKDB on your data to check for potential corruption. Running CHECKDB regularly against your production databases will detect corruption quickly. Thus providing a better chance to recover valid data from a backup, or being able to repair the corruption. CHECKDB will check the logical and physical integrity of the database by running these three primary checks*:
- CHECKALLOC – checks the consistency of the database;
- CHECKTABLE – checks the pages and structures of the table or indexed view; and
- CHECKCATALOG – checks catalog consistency.
Where to Look
Wondering if you have missing integrity checks or if they have ever been performed on your database? The following T-SQL script will show when/if integrity checks were performed on your databases. (Bonus) Running this script regularly will help track down missing integrity checks.
If you are looking for the last date the DBCC checks ran, the T-SQL script to use is as follows:
IF OBJECT_ID('tempdb..#DBCCs') IS NOT NULL
DROP TABLE #DBCCs;
CREATE TABLE #DBCCs
(
ID INT IDENTITY(1, 1)
PRIMARY KEY ,
ParentObject VARCHAR(255) ,
Object VARCHAR(255) ,
Field VARCHAR(255) ,
Value VARCHAR(255) ,
DbName NVARCHAR(128) NULL
)
/*Check for the last good DBCC CHECKDB date */
BEGIN
EXEC sp_MSforeachdb N'USE [?];
INSERT #DBCCs
(ParentObject,
Object,
Field,
Value)
EXEC (''DBCC DBInfo() With TableResults, NO_INFOMSGS'');
UPDATE #DBCCs SET DbName = N''?'' WHERE DbName IS NULL;';
WITH DB2
AS ( SELECT DISTINCT
Field ,
Value ,
DbName
FROM #DBCCs
WHERE Field = 'dbi_dbccLastKnownGood'
)
SELECT @@servername AS Instance ,
DB2.DbName AS DatabaseName ,
CONVERT(DATETIME, DB2.Value, 121) AS DateOfIntegrityCheck
FROM DB2
WHERE DB2.DbName NOT IN ( 'tempdb' )
END
The result will look similar to this. However, let’s hope your results show a date closer to today’s date than my own! If you see that your databases do not have integrity checks in place, check your backup and recovery plans and double check your agent jobs to see if perhaps the checks were scheduled but were turned off.
Recommendations
It is recommended that DBCC CHECKDB is run against all production databases on a regular schedule. The best practice is to have this automated and scheduled as a SQL Agent job to run as a regular part of maintenance. More specifically, to run the integrity check directly before purging any full backups. Doing so will ensure that corruption is detected quickly, which will give you a much better chance to recover from your backup or being able to repair the corruption.
Remember, SQL Server is very forgiving and will back up a corrupt database! Corrupt databases are recoverable, but they might have data pages that are totally worthless!
