whether it’s a new job or a new (old) server
If you’re reading this, you’ve at some point in your career stared at a server, for the first time, with great trepidation.
The smarter you are, the greater your trepidation is. The 2nd century mathematician Trepidatius the Wimpy had an equation that described this, but he only applied it to leaving his hut.
So the first thing you check is backups. Miraculously, someone is at least taking FULL backups. The logs and diffs are another story, but that’s why you’re getting paid. If your DBA checklist looks like mine, the next box down is seeing if someone has ever run DBCC CHECKDB to find corruption.
BUT HOW?
Since it’s my favorite test data set, I’ll use the StackOverflow database.
Ready?
|
1 |
DBCC DBINFO('StackOverflow') WITH TABLERESULTS |
That’s it. But the output is a nightmare. It’s about 80 lines of stuff you will probably never care about. Around line 50 is what you’re looking for.
And this is probably what you’ll see! A date of 1900-01-01 etc. That means never. If you run DBCC CHECKDB on the database, perhaps like so:
|
1 |
DBCC CHECKDB('StackOverflow') WITH NO_INFOMSGS, ALL_ERRORMSGS |
And then re-run the DBCC DBINFO command, our date is now updated to current:
IS THIS THE ONLY WAY?
Of course not. But if you need a quick solution, there it is. The only catch is that it will update if you run your DBCC CHECKDB with PHYSICAL_ONLY set. Using that option skips the logical consistency checks that a full run of DBCC CHECKDB does.
If you’re a smarty pants, and you’re using Ola Hallengren’s maintenance scripts, you can check the CommandLog table it creates to [drumroll] log commands, see when DBCC CHECKDB was last run, and even how long it took.
If you’re doing something else, some guy named Brent wrote a stored procedure called sp_Blitz® that will tell you if any of the databases on your server have not had a consistency check run in the last two weeks. It will also tell you everything else wrong with that new server. It was one of my favorite things in the world, back when I had a real job.
If you like this stuff, and you want to get better at it, we’ll show you how! Join us for our upcoming Senior DBA Class in crime-free Chicago.
Kendra says: Ever been confused by those weird messages about CHECKDB in the SQL Server log when your instance starts up, but sometimes it might show a really old date? Fun fact: it’s actually looking up last CHECKDB run date for the database.
27 Comments. Leave new
You can also use the built-in reports in SSMS. Right click database, Reports, Standard Reports, Database Consistency History. However, since it reports off the default trace, it may be less reliable depending on workload.
Good one Erik!
Another little gotcha is that [dbi_dbccLastKnownGood] does not get updated when the database is READ_ONLY, so another way of logging might help if you have that case.
Also considering you’re using DBCC DBINFO() to get info about last good, it might be good to add some comments about the ‘data purity flags’ dbi_dbccFlags
http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-how-to-tell-if-data-purity-checks-will-be-run/
That’s another big gotcha!
Cheers.
Bonus. And also you can get that using DBCC PAGE(‘StackOverflow’, 1, 9, 3) WITH TABLERESULTS
Heres a quick little query I use for quickly checking all the databases on an instance:
CREATE TABLE #DBInfo (ParentObject VARCHAR(255), [Object] VARCHAR(255), Field VARCHAR(255), [Value] VARCHAR(255))
CREATE TABLE #Value (DatabaseName VARCHAR(255), LastDBCCCheckDB DATETIME)
EXECUTE sp_MSforeachdb ‘
— Insert results of DBCC DBINFO into temp table, transform into simpler table with database name and datetime of last known good DBCC CheckDB
INSERT INTO #DBInfo EXECUTE (”DBCC DBINFO ( ””?”” ) WITH TABLERESULTS”);
INSERT INTO #Value (DatabaseName, LastDBCCCheckDB) (SELECT ”?”, [Value] FROM #DBInfo WHERE Field = ”dbi_dbccLastKnownGood”);
TRUNCATE TABLE #DBInfo;
‘
SELECT * FROM #Value
Ryan, I really like your script that checks last DBCC of ALL databases on a instance, however, I’m getting duplicated results for each database. For example, on an instance with 4 databases, I get 2 rows for each database for a total of 8 rows, instead of 4 rows as expected.
Xp_ReadErrorLog also shows this info 🙂
Something like this:
2015-08-01 13:19:22.280 spid20s CHECKDB for database ‘msdb’ finished without errors on 2015-04-12 18:47:58.410 (local time). This is an informational message only; no user action is required.
or view the bootpage (9) of a databasefile:
DBCC TRACEON(3604); –get the results of the DBCC PAGE command on screen
DBCC PAGE(0,1,9,3);
Don’t forget to turn that off!
DBCC TRACEOFF(3604);
@Erik, as you know, only DBCC TRACEON(3604, -1) will set the trace globally, although it’s always a good idea to disable any flag you use, in this case it’d just die with the session… anyway it’s a good idea just in case 🙂
@Eelco, completely true, but for scripting purposes I believe is more usable WITH TABLERESULTS, also there is no need for activating any flag.
DBCC CHECKFILEGROUP will also update the last good DBCC value: http://www.sqlsoldier.com/wp/sqlserver/day22of31daysofdisasterrecoverywhichdbcccheckcommandsupdatelastknowngooddbcc
Now that’s just silly.
Jason Brimhall wrote a world-beater script to list the most recent, successful, DBCC CheckDB date/time for each database on the instance. He modestly called it “just another tool to throw into the toolbox” and it’s at the top of my toolbox (used weekly after DBCC CheckDB on every SQL Server instance). You may find Jason’s script here: http://jasonbrimhall.info/2012/11/20/last-known-good-checkdb/
Wonderful!
However in 2008R2 have a duplicate record for dbi_dbccLastKnownGood.
Same result for DBCC PAGE
Any idea?
Slava – I’m not seeing that here, so it may be something unique to your system or the way you’re querying it. Sorry!
No problem. DBCC CHECKDB hasn’t been done for that system since April 2014. Responsible DBA says: “-DBCC CHECKDB blocked user’s tasks and we turned it off. It is wonderful since then. Why do we need it at all?”
I’ll send him the link to Kendra’s article tomorrow.
BTW. Will check if duplication goes away after we perform the check and will do more research on that.
Well, that doesn’t sound very responsible.
DBCC CHECKDB shouldn’t cause blocking, necessarily. Unless you run it with TABLOCK, it takes a snapshot of the database, and runs a consistency check on that.
It can be resource intensive though, which could totally slow users down. If it’s a problem running it in-place on your production server, you should look at restoring full backups to another server and running your DBCC checks there.
Stored procedure, which run DBCC CHECKDB, also run DBCC UDATEUSAGE for each DB. That was an evil.
If it won’t fit in prod I’ll suggest to run against the copy.
We have several databases that fit this problem, and run DBCC on a restored copy of the database on another server. Too bad the production database still has to show the old (or no) last execution date.
SteveA, only caveat on that you have to pay for SQL license on the second server.
I have a 2008 R2 standard edition and Noticed that dbi_dbccLastKnownGood value got updated even by executing DBCC CHECKDB(‘DatabaseName’) WITH ESTIMATEONLY. How to differentiate the actual one from the estimate ?
I can’t reproduce this on 2012/2014/2016. Sorry!
Maybe it’s time to get out of the last decade? 🙂
If you want to find out the last CHECKDB-Run without using a SysAdmin account, you could use the following statement (replace e:\MSSQL12.MSSQLSERVER\MSSQL\Log\ by your log directory).
Remarks:
– the user that runs the statment needs at least the ALTER TRACE permission (GRANT ALTER TRACE TO MyMonitorUser)
– when you truncate the log file more often than you do CHECKDB then it would return nothing
select SUBSTRING(convert(nvarchar(MAX),TextData),36, patindex(‘%executed%’,TextData)-36) as command
, LoginName
, StartTime
, convert(int,substring(convert(nvarchar(MAX),TextData),patindex(‘%found%’,TextData)+6,patindex(‘%errors %’,TextData)-patindex(‘%found%’,TextData)-6)) as errors
, convert(int,substring(convert(nvarchar(MAX),TextData),patindex(‘%repaired%’,TextData)+9,patindex(‘%errors.%’,TextData)-patindex(‘%repaired%’,TextData)-9)) repaired
, substring(convert(nvarchar(MAX),TextData),patindex(‘%time:%’,TextData)+6,patindex(‘%hours%’,TextData)-patindex(‘%time:%’,TextData)-6)+’:’+substring(convert(nvarchar(MAX),TextData),patindex(‘%hours%’,TextData)+6,patindex(‘%minutes%’,TextData)-patindex(‘%hours%’,TextData)-6)+’:’+substring(convert(nvarchar(MAX),TextData),patindex(‘%minutes%’,TextData)+8,patindex(‘%seconds.%’,TextData)-patindex(‘%minutes%’,TextData)-8) as time
from ::fn_trace_gettable( ‘e:\MSSQL12.MSSQLSERVER\MSSQL\Log\log.trc’, default )
where EventClass = 22 and substring(TextData,36,12) = ‘DBCC CHECKDB’ and DatabaseName = ‘master’;
PS: source for the statement is the Database consistency history report in SSMS (Standard report at database level)
The trouble with this is that your trace data may not go back to when you last ran CHECKDB (which, granted, may be a problem all its own).
Try just selecting the MIN and MAX StartTime from your trace.
I got 2017-02-10 20:15:24.550, and 2017-02-21 08:08:27.030, and my server isn’t terribly busy 🙂
Here’s a quick solution similar to Ryan’s:
–get last DBCC CHECKDB date
–Temp table to hold pivot results
CREATE TABLE #Results (ID SMALLINT IDENTITY(1,1), DatabaseName NVARCHAR(100), CompatibilityLevel INT, LastCheckDB DATETIME)
–stage
CREATE TABLE #DBInfo (ID SMALLINT IDENTITY(1,1), ParentObject NVARCHAR(50), [Object] NVARCHAR(100), [Field] NVARCHAR(100), [Value] NVARCHAR(100))
EXEC sp_MSforeachdb ‘USE [?];
INSERT INTO #DBInfo
EXEC (”DBCC DBINFO() WITH TABLERESULTS”)
INSERT INTO #Results
SELECT [dbi_dbname] AS DatabaseName, [dbi_cmptlevel] AS CompatibilityLevel, [dbi_dbccLastKnownGood] AS LastCheckDB
FROM ( SELECT Field, Value FROM #DBInfo) Src
PIVOT ( MAX(Value) FOR Field IN (dbi_cmptlevel, dbi_dbname, dbi_dbccLastKnownGood)
) piv;
TRUNCATE TABLE #DBInfo;
‘
SELECT * FROM #Results
DROP TABLE #Results
DROP TABLE #DBInfo
[…] you look for what you can run to find when CHECKDB was last run you find this blog post and also this blog post on grabbing this info. While these were very informative, they were for one database at a time. I […]
Small Update:
IF OBJECT_ID(‘tempdb..#DBInfo’) IS NOT NULL
Drop TABLE #DBInfo
IF OBJECT_ID(‘tempdb..#Value’) IS NOT NULL
Drop TABLE #Value
CREATE TABLE #DBInfo (ParentObject VARCHAR(255), [Object] VARCHAR(255), Field VARCHAR(255), [Value] VARCHAR(255))
CREATE TABLE #Value (DatabaseName VARCHAR(255), LastDBCCCheckDB DATETIME)
EXECUTE sp_MSforeachdb ‘
— Insert results of DBCC DBINFO into temp table, transform into simpler table with database name and datetime of last known good DBCC CheckDB
INSERT INTO #DBInfo EXECUTE (”DBCC DBINFO ( ””?”” ) WITH TABLERESULTS”);
INSERT INTO #Value (DatabaseName, LastDBCCCheckDB) (SELECT ”?”, [Value] FROM #DBInfo WHERE Field = ”dbi_dbccLastKnownGood”);
TRUNCATE TABLE #DBInfo;
‘
SELECT * FROM #Value
WHERE DatabaseName NOT IN (‘tempdb’)
IF OBJECT_ID(‘tempdb..#DBInfo’) IS NOT NULL
Drop TABLE #DBInfo
IF OBJECT_ID(‘tempdb..#Value’) IS NOT NULL
Drop TABLE #Value
dbatools to the rescue.
https://docs.dbatools.io/#Get-DbaLastGoodCheckDb
e.g. Get databases without a good DBCC CHECKDB in past 48 hours:
Get-DbaLastGoodCheckDb -SqlInstance ‘SQL01’ | ?{$_.Status -notin (‘Ok’,’New database, not checked yet’) -and $_.LastGoodCheckDb -lt (Get-Date).AddDays(-2)}
Richard — yep, take a look at what the T-SQL there translates into.
https://github.com/sqlcollaborative/dbatools/blob/master/functions/Get-DbaLastGoodCheckDb.ps1#L117
Thanks!