Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Is there a way to check when was the last integrity check run for each database?

I know when the service startup it checks for these values, so they exist, and they are probably writen on the database header information.

By the way I would like an T-SQL solution.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

You can get this information from the Boot Page of the database. i.e. Page ID 9.

DBCC TRACEON (3604)
GO
DBCC PAGE (DatabaseName, 1, 9, 3)
GO
share|improve this answer
    
This is what I was looking for, I've checked and its the dbi_dbccLastKnownGood field that says what is the last dbcc checkdb successfull date. Thanks @Pradeep –  Gabriel Guimarães Dec 8 '10 at 12:01
add comment

When the SQL Server starts up, it reports the last DBCC CHECKDB date as an informational message. It is not running the command, just reporting.

To see from T-SQL this execute this

EXEC sp_readerrorlog 0, 1, 'CHECKDB'

This procedure takes four parameters:

  1. Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: String two you want to search for to further refine the results
share|improve this answer
    
Nice solution but If I want to know this per database I'll have to check the Database Boot Page, so the solution I was looking for was the Boot Page one provided by @Pradeep. But +1 for the solution its great, I didn't know there was a way for reading the Log that was not the fn_dblog one. –  Gabriel Guimarães Dec 8 '10 at 11:58
    
SURE - I in fact didn't know about boot page solution. It's nice to know. Anyhow, you can check per database as well reading the log file if you setup a second string to search: name of the database for ex: EXEC sp_readerrorlog 0, 1, 'CHECKDB', 'database'. You'll have the information only for this single db you are interested. Thanks for the support:) –  yrushka Dec 8 '10 at 13:47
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.