Checking DBCC CHECKDB

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

checkdb

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

checkdb2.JPG

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.

checkdb3.JPG

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.

physicalonly.JPG

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

checkdb4.JPG

DBCC CHECKCATALOG
GO

DBCC DBINFO WITH TABLERESULTS

checkdb4.JPG

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

checkdb4.JPG

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

checkdb4

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.

checkdb6.JPG

There you have it – the updated value.

3 thoughts on “Checking DBCC CHECKDB

  1. Pingback: Checking DBCC CHECKDB - SQL Server Blog - SQL Server - Toad World

  2. Hello guys, I am Eva Mccarthy an expert in content writing.

    I enjoy solving people’s problems and make them happy. That is what I have been doing for many years now.
    I have been writing since I was 12 years old and never knew it would turn out to be a full-time career. I have also been able to manage several projects that involves writing. And I worked in three organizations as a volunteer to assist people.My passion has always been to help people succeed. And I go the extra mile to make that happen.
    I enjoy writing Thesis and have helped people from countries like USA.
    I work with a company whose mission is to provide quality works and make people happy. In fact, many clients come to me for help on a daily basis because they know I always deliver. And I will continue to provide nothing but quality to build trust like I have been doing for the past few years.

    Expert writer – Eva – tritronicsinc.com Corp

    Like

  3. Hello All
    Thanks for checking out my academic writing page . My name is Jody.
    I have worked since high school in this niche. My passion for writing started at a young age. I wrote short stories as a child and eventually went on to work with my school newspaper.
    This early tryst into news reporting eventually led me to academic writing. There is plenty of work for qualified writers. I specialize in essays, but have the skills to do all types of academic writing.
    Reach out for more information about rates and a price quote. I’m looking forward to helping you.

    Academic Writer – Jody – Company

    Like

Leave a Reply to evamccarthy Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s