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.
Pingback: Checking DBCC CHECKDB - SQL Server Blog - SQL Server - Toad World
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
LikeLike
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
LikeLike