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