When setting up a new SQL Server, it is crucial to run a CHECKDB on the databases. This simple command, “DBCC CHECKDB()”, checks the logical and physical integrity of all objects in the specified database. By running this command, you can identify any corruption that may have occurred and pinpoint the exact location of the problem.

DBCC CHECKDB has optional arguments that can help fix corrupt databases. In some cases, you may need to use the “REPAIR_ALLOW_DATA_LOSS” argument, which fixes the database but may result in data loss. It is always recommended to try recovering the database using a backup before resorting to this argument. Other corruptions, such as inconsistencies between tables and indexes, can be fixed without data loss using the “REPAIR_REBUILD” argument.

To run DBCC CHECKDB, simply execute the following command:

DBCC CHECKDB (AdventureWorks2008R2)
GO

By default, this command will output informational messages, including the number of rows and pages for each object in the database, as well as any errors or inconsistencies found. If corruption is detected, the command will raise an error and highlight the problem.

Not running the CHECKDB command can have serious consequences. Without regular checks, you may never know if something is wrong until it’s too late. For example, if corruption occurs in a table that is not frequently accessed, it could take weeks or even months to discover the issue. If your backup retention does not go back far enough, you may lose valuable data without any means of recovery.

To avoid such scenarios, it is recommended to run DBCC CHECKDB regularly. You can schedule it using a maintenance plan to run daily, weekly, or every two weeks. Alternatively, you can create a SQL Agent job that executes the following command:

exec master.dbo.sp_msforeachdb "DBCC CHECKDB([?]) WITH NO_INFOMSGS, ALL_ERRORMSGS"

This command will run DBCC CHECKDB on all databases. If an error occurs in any of them, the SQL Agent job will fail, and you can set up an email notification using Database Mail to alert you.

If you want to monitor when a database last performed a successful CHECKDB, you can use the DBCC DBINFO command. For example:

DBCC DBINFO (AdventureWorks2008R2) WITH TABLERESULTS

This command will provide information about the database, including a row with the field “dbi_dbccLastKnownGood” indicating the last successful CHECKDB.

To monitor this for all your databases, you can use the following script:

CREATE TABLE #tempTotal (DatabaseName varchar(255), Field VARCHAR(255), Value VARCHAR(255))
CREATE TABLE #temp (ParentObject VARCHAR(255), Object VARCHAR(255), Field VARCHAR(255), Value VARCHAR(255))

EXECUTE sp_MSforeachdb '
INSERT INTO #temp EXEC(''DBCC DBINFO ('''''?''''') WITH TABLERESULTS'')
INSERT INTO #tempTotal (Field, Value, DatabaseName)
SELECT Field, Value, ''?'' FROM #temp
TRUNCATE TABLE #temp';

WITH cte AS (
SELECT ROW_NUMBER() OVER (PARTITION BY DatabaseName, Field ORDER BY Value DESC) AS rn, DatabaseName, Value
FROM #tempTotal t1
WHERE (Field = 'dbi_dbccLastKnownGood')
)
SELECT DatabaseName, Value AS dbccLastKnownGood
FROM cte
WHERE (rn = 1)

DROP TABLE #temp
DROP TABLE #tempTotal

In conclusion, it is essential to set up a scheduled DBCC CHECKDB task on your SQL Server. Regularly running this command will alert you to any database corruption, allowing you to take immediate action. Without this crucial check, you may not discover the problem until it’s too late, which is a situation you want to avoid at all costs.