SQL Server: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:
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
How often checkdb should be run against database?
I would recommend to run CHECKDB against database once in a day in the midnight.
As part of regular database maintenance job i scheduled to run checks the logical and physical integrity of all the objects in the specified database.
when you run dbcc checkdb performing the following opearation:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
eg:
-- Check the AdventureDb database.
DBCC CHECKDB (AdventureDb)
GO
--Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.
USE AdventureWorks
GO
DBCC CHECKDB WITH PHYSICAL_ONLY
-- Check the AdventureWorks database without nonclustered indexes.
DBCC CHECKDB (AdventureWorks, NOINDEX);
GO
As part of regular database maintenance job i scheduled to run checks the logical and physical integrity of all the objects in the specified database.
when you run dbcc checkdb performing the following opearation:
Runs DBCC CHECKALLOC on the database.
Runs DBCC CHECKTABLE on every table and view in the database.
Runs DBCC CHECKCATALOG on the database.
Validates the contents of every indexed view in the database.
eg:
-- Check the AdventureDb database.
DBCC CHECKDB (AdventureDb)
GO
--Specifying PHYSICAL_ONLY causes DBCC CHECKDB to skip all checks of FILESTREAM data.
USE AdventureWorks
GO
DBCC CHECKDB WITH PHYSICAL_ONLY
-- Check the AdventureWorks database without nonclustered indexes.
DBCC CHECKDB (AdventureWorks, NOINDEX);
GO