Monday, May 12, 2014

When was CHECKDB last run on your databases?

Hopefully, as SQL Server DBAs, we all know the value of CHECKDB.  I started a new contract recently, and found that it had never been run.  When I asked, I was just told that 'we didn't think it was that important'.  Long story short, CHECKDB consistency checks allow us to proactively monitor for database corruption, and maintain the integrity of our databases.  Proactive awareness is far better than reactive.  In my book, I don't think you can run it too frequently.  

Here's a quick way to confirm when CHECKB was last run on your databases.  

-- A couple temp tables 
CREATE TABLE #DBInfo (
  ID INT IDENTITY(1,1), 
  ParentObject VARCHAR(255),
  Object VARCHAR(255),
  Field VARCHAR(255),
  Value VARCHAR(255) 
)

CREATE TABLE #Value(
  DatabaseName VARCHAR(255),
  LastCheckDB VARCHAR(255)
)

-- Populate temp tables
EXECUTE sp_msforeachdb 'INSERT INTO #DBInfo Execute (''DBCC DBINFO ( ''''?'''') WITH TABLERESULTS'');
INSERT INTO #Value (DatabaseName) SELECT Value FROM #DBInfo WHERE Field IN (''dbi_dbname'');
UPDATE #Value 
SET LastCheckDB = (SELECT TOP 1 Value FROM #DBInfo WHERE Field IN (''dbi_dbccLastKnownGood'')) 
WHERE LastCheckDB IS NULL;
TRUNCATE TABLE #DBInfo';

-- Return the output 
SELECT * FROM #Value

-- Drop temp tables 
DROP TABLE #DBInfo
DROP TABLE #Value


These are the results from my own instance:

       Database                        LastCheckDB
       master                             2014-05-12 11:26:20.610
       tempdb                           1900-01-01 00:00:00.000 --- no biggie. no need to check tempdb
       model                              2014-05-12 14:52:32.743
       msdb                               2014-05-12 14:52:34.053
       Mine                               2014-05-12 19:58:46.717
       AdventureWorks2012       2014-05-12 19:58:38.850



If you are not running CHECKDB consistency checks regularly, I strongly encourage you to put something into place -- soon!  You can take a look at my User Database Maintenance posting:   
    http://www.sqlfingers.com/2011/01/user-database-maintenance.html

Or take a look at Ola Hallengren's site for a much larger overview of the approach that I use in my maintenance tasks:    
    http://ola.hallengren.com/sql-server-integrity-check.html

No comments:

Post a Comment