0

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.

Gabriel Guimarães
  • 405
  • 5
  • 18

2 Answers2

1

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
Nomad
  • 471
  • 2
  • 3
  • 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 08 '10 at 12:01
1

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
yrushka
  • 600
  • 2
  • 9
  • 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 08 '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 08 '10 at 13:47