SQL ,Are DBCC's being done?

SQL ,Are DBCC's being done?

DBCC CHECKDB (Transact -SQL)       per Books Online
Checks the logical and physical integrity of all the objects in the specified database by performing the following operations:

• 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.
• Validates link-level consistency between table metadata and file system directories and files when storing varbinary(max) data in the file system using FILESTREAM.
• Validates the Service Broker data in the database.

aka, Just Do It.

Ok, that's not fair.  You'd need to be in the SQL seat for a good amount of time before you understand the woes of not doing it.  (if you're lucky)  I strongly advocate using CHECKDB hand in hand with a strong backup strategy, to protect the integrity and stability of your database(s).  It can alert you of problems you weren't even aware of, and it can repair problems, if needed.  And please remember, disk failures CAN impact the integrity and availability of your data.  It can even corrupt your data.  CHECKDB will bring this to your attention.

Anyway, if you inherit some servers, you may want to see if DBCC's are being run:

  CREATE TABLE #working ([Parent] VARCHAR(255),[Object]    

  VARCHAR(255),[Field] VARCHAR(255),[Value] VARCHAR(255)   )

  CREATE TABLE #DBCCdetails (ServerName VARCHAR(255),DatabaseName    

  VARCHAR(255),LastRan DATETIME   )   

    

  EXEC master.dbo.sp_MSforeachdb       

     @command1 = 'USE [?] INSERT INTO #working EXECUTE (''DBCC  

  DBINFO WITH TABLERESULTS'')',

     @command2 = 'INSERT INTO #DBCCdetails SELECT @@SERVERNAME, 

  ''?'', Value FROM #working WHERE Field = 

  ''dbi_dbccLastKnownGood''',

     @command3 = 'TRUNCATE TABLE #working'

   

  ;WITH DBCC_CTE AS

  (

    SELECT ROW_NUMBER() OVER (PARTITION BY ServerName,  

    DatabaseName,LastRan ORDER BY LastRan) RowID

    FROM #DBCCdetails

  )

    DELETE FROM DBCC_CTE WHERE RowID > 1;

   

    SELECT        

       ServerName,

       DatabaseName,       

       CASE LastRan WHEN '1900-01-01 00:00:00.000' 

         THEN 'DBCC CHECKDB has NEVER been run.' 

       ELSE CAST(LastRan AS VARCHAR) END AS LastRan

    FROM #DBCCdetails

    ORDER BY 3;

   

    DROP TABLE #working;

    DROP TABLE #DBCCdetails;

I would do it, anyway.  In fact, I do.  I use this, or a version of it, every time I inherit new databases.


NOTES:

• The TRUNCATE in @command3 is necessary due to a bug in v2008 that produces duplicates with the DBINFO insert.  
• DBCC CHECKDB is much heavier in v2008 than it was in previous engines, due to the more comprehensive logical checks, and more complex underlying structures.  MSFT recommends using PHYSICAL_ONLY on larger databases, for a shorter runtime.  The difference in overhead is very notable!  I commonly use PHYSICAL_ONLY for the intra-week maintenance, and do a full one on Saturdays.