I hope you run consistency checks on your databases, if you are not currently doing this you probably will want to.

For this post I want to show you how you can check whether or not CHECKDB has been successfully executed.

There are a couple of ways to check this but I will be using DBCC DBINFO for this check.

DBCC DBINFO WITH TABLERESULTS

The field of interest here is dbi_dbccLastKnownGood where VALUE of 1900-01-01 00:00:00.000 means that DBCC CHECKDB has never been executed (successfully).

So let’s run CHECKDB and see the value get updated.

DBCC CHECKDB ('ZoraDB')
GO

DBCC DBINFO WITH TABLERESULTS

The next question I want answering is whether or not this value gets updated if I run CHECKDB with the physical_only option (I created a fresh copy of the database and performed a re-check)

USE [master]
GO

DROP DATABASE [ZoraDB]
GO

CREATE DATABASE [ZoraDB]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'ZoraDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ZoraDB.mdf' , SIZE = 5120KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'ZoraDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ZoraDB_log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

DBCC DBINFO WITH TABLERESULTS

Below confirms that we are back to a newly created database.

So we now run it with the PHYSICAL_ONLY option.

DBCC CHECKDB ('ZoraDB') WITH PHYSICAL_ONLY
GO
USE [ZoraDB]
GO
DBCC DBINFO WITH TABLERESULTS

So, stating physical_only updates the value too.

What about separate checks?

Again I dropped and re-created the database to get back to level playing field.

Let’s work through the DBCC check commands, after a command I will check dbi_dbccLastKnownGood to see if it gets updated.

DBCC CHECKALLOC
GO
DBCC DBINFO WITH TABLERESULTS

DBCC CHECKCATALOG
GO

DBCC DBINFO WITH TABLERESULTS

DBCC CHECKTABLE ('dbo.people')
GO
DBCC DBINFO WITH TABLERESULTS

So checking the database via separate checks has no impact on dbi_dbccLastKnownGood.

What about if the database is corrupted? I re-created the database and corrupted it:

DBCC CHECKDB ('ZoraDB')
GO

CHECKDB found 0 allocation errors and 2 consistency errors in database ‘ZoraDB’.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (ZoraDB).
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

DBCC DBINFO WITH TABLERESULTS

Naturally it will not get updated because it was unsuccessful – If we fixed the corruption and ran CHECKDB it would then get updated with the current timestamp.

DO NOT RUN REPAIR WITH THIS OPTION – THIS IS AN EXAMPLE ONLY! It deleted my data!! THIS IS A LAST RESORT – WARNING.

ALTER DATABASE [ZoraDB] SET SINGLE_USER;
GO

DBCC CHECKDB    ('ZoraDB'  , REPAIR_ALLOW_DATA_LOSS )

ALTER DATABASE [ZoraDB] SET MULTI_USER;
GO

DBCC CHECKDB ('ZoraDB')
GO

CHECKDB found 0 allocation errors and 0 consistency errors in database ‘ZoraDB’.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

There you have it – the updated value.