Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Check DBCC CHECKDB Last Execution using PowerShell


By:   |   Read Comments (15)   |   Related Tips: More > Database Consistency Checks DBCCs

Problem

I want to check when DBCC CHECKDB was last executed on all of my SQL Server databases. However, most of the solutions I see online involve the creation of temporary tables and/or a combination of techniques to get the date and time of the last DBCC CHECKDB execution. Is there a much easier solution?  Check out this tip to learn more.

Solution

We SQL Server DBAs know the importance of running regular consistency checks to make sure that we verify the integrity of our databases. If we have a new SQL Server instance, we can simply implement a SQL Server Agent job that runs DBCC CHECKDB on a regular basis. But what if we need to check an existing SQL Server instance that has been running for quite some time? We can't just rely on existing SQL Server Agent jobs that perform consistency checks because someone could have them disabled and re-enabled back, causing lapses in the consistency checks. And while the SQL Server error log could be another way to check for the last execution of DBCC CHECKDB against a database, it gets recycled every service restart and older logs are overwritten. The most reliable way will be to read the database boot page using the undocumented command DBCC DBINFO to check the last DBCC CHECKDB execution. Similar to the previous tip on Check SQL Server Virtual Log Files Using PowerShell, most of the solutions I see online require a combination of temporary tables and/or cursors to accomplish this task. Thankfully, we have PowerShell available for us to use with SQL Server Management Objects (SMO) to make this task a bit simpler. While we do not have an equivalent method in SMO for the DBCC DBINFO command (after all, it is still undocumented), we can take advantage of the ExecuteWithResults method of the Database class.

Let's see how we can simplify this task by using PowerShell and SMO. We'll start by loading the appropriate .NET assemblies used by SMO as defined in this previous tip.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

Next, we'll create an instance of the Server class to represent the SQL Server instance that you will be connecting to, passing the SQL Server instance name. I will be assigning the results to a variable named $serverInstance so we can access the different properties and methods of the Server class.

$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "InstanceName"

We will, then, iterate thru all of the databases in the SQL Server instance by accessing the Databases property of the Server class. This property represents a collection of database objects defined in the SQL Server instance we've specified. While iterating thru the collection of databases, we will use the ExecuteWithResults method of the Database class to execute the DBCC DBINFO command in the context of the current database (we will use the WITH TABLERESULTS option used with DBCC commands to filter according to a specific field value.) The results of this method is a .NET object type called a DataSet which is commonly used in ADO.NET. A DataSet is an in-memory cache of data with a collection of DataTable objects. Think of it as an in-memory version of a database with different tables, columns, relationships, etc. If there are multiple tables in the resultset, we can access each table using an index in an array. However, since the results of the DBCC DBINFO command is a single resultset that mimics a table, think of the results of the ExecuteWithResults method as an in-memory table. The PowerShell code to accomplish all of these tasks is shown below.

foreach($db in $serverInstance.Databases)
{
$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0]
}

In order to access the table results, I used the zero index since I did not explicitly define the name of the DataTable that will hold the results of the DBCC DBINFO command. The results of running the DBCC DBINFO T-SQL command is shown below.

The results of running the DBCC DBINFO T-SQL command is shown below

The results of running the PowerShell script is shown below.

The results of running the PowerShell script

I've highlighted the field that we are interested in - dbi_dbccLastKnownGood. This field contains the value of the last DBCC CHECKDB execution. We need to filter the result set of the DBCC DBINFO command to display only this field and its corresponding value. This is where the temporary tables and/or cursors start appearing in most of the solutions we see online. Fortunately, PowerShell has the Where-Object cmdlet to filter the results of object collections based on their property values. We can use the Where-Object cmdlet to filter the results returned by the ExecuteWithResults method for a particular database and use the field named dbi_dbccLastKnownGood. To make it easier, we will pipe the results of the ExecuteWithResults method to the Where-Object cmdlet. I've also included the Select-Object cmdlet to display the name of the database and the value of the dbi_dbccLastKnownGood field.

foreach($db in $serverInstance.Databases)
{
$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"}  | Select-Object $db.Name, Value
}

Windows PowerShell code to with the Select-Object cmdlet to capture the dbi_dbccLastKnownGood value

NOTE: In the screenshot, I introduced line breaks for ease of readability. However, the command can be written in a single line of code, which is one of the key strengths of Windows PowerShell.

The result now displays how I want - the name of the database and the date and time when DBCC CHECKDB was last executed against a database - in just 7 lines of PowerShell code (I can even write this in 3 lines and have the entire foreach loop in a single line, but that would be annoying and hard to read) that does not use temporary tables and/or cursors.

Here's the complete code listing.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost"
foreach($db in $serverInstance.Databases)
{
$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object $db.Name, Value
}

Checking Against Policies

But, let's take this a step further. Let's say that you schedule your DBCC CHECKDB execution every week. If you have hundreds of databases on a SQL Server instance, you only want to retrieve a list of databases that are out of compliance. We can use date arithmetic with the different PowerShell cmdlets to get the number of days between the last DBCC CHECKDB execution and today's date. Let's first assign the results of the ExecuteWithResults method in a variable named $lastDBCC_CHECKDB.

$lastDBCC_CHECKDB = $db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object $db.Name, Value

Next, let's calculate the number of days between today's date (using the Get-Date cmdlet) and the last DBCC CHECKDB execution date. We will assign this value to the $DaysOld variable.

$DaysOld = ((Get-Date) - [DateTime]$lastDBCC_CHECKDB.Value).Days

We, then, check if the last DBCC CHECKDB execution was greater than seven (7) days using the $DaysOld variable.

#Check if date of last DBCC CHECKDB execution is greater than 7 days
if($DaysOld -gt 7)
{
    Write-Host $db.Name " Last DBCC CHECKDB execution : " $lastDBCC_CHECKDB.Value
}

Here's the complete code listing with the check for the number of days since the last DBCC CHECKDB execution - in 13 lines of code.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost"
foreach($db in $serverInstance.Databases | Where-Object {$_.Name -ne "tempdb"}) #Exclude the tempdb database
{
$lastDBCC_CHECKDB=$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where {$_.Field.ToString() -eq "dbi_dbccLastKnownGood"} | Select $db.Name, Value
    $DaysOld = ((Get-Date) - [DateTime]$lastDBCC_CHECKDB.Value).Days
    if($DaysOld -gt 7)
    {
     write-host $db.Name " Last DBCC CHECKDB execution : " $lastDBCC_CHECKDB.Value
    }
}

Here's the complete code listing with the check for the number of days since the last DBCC CHECKDB execution - in 13 lines of code
Next Steps


Last Update:






About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

View all my tips
Related Resources


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Wednesday, September 11, 2013 - 11:48:05 AM - Emeline Back To Top

duplicates rows due to a bug in SQL Server 2008

use $lastDBCC_CHECKDB[0].Value


Wednesday, June 05, 2013 - 12:47:30 PM - bass_player Back To Top

BTW, the dbi_dbccLastKnownGood field does not exist on a SQL Server 2000 database so the script only works on SQL Server 2005 and higher


Tuesday, June 04, 2013 - 10:33:42 AM - bass_player Back To Top

Hi Jens,

Thanks for pointing that out. Indeed, it will only display the last known good date and time that the DBCC CHECKDB ran successfully. It will not update the boot page if it found corruptions on the database. This is why I included a check to see how many days have passed since the last time that DBCC CHECKDB ran successfully. For example, if DBCC CHECKDB is scheduled to run every Sunday and it found corruption on the database, the boot page will not get updated. If you run the PowerShell script on Monday, it will display all of the databases that have not had a successful DBCC CHECKDB execution for ore than 7 days, alerting the DBA to re-run DBCC CHECKDB and potentially find the corruption.


Tuesday, June 04, 2013 - 7:30:02 AM - Jens Vestergaard Back To Top

Please bare in mind, that dbi_dbccLastKnownGood only show the last time DBCC was executed successfully.
Any failed runs will not be logged in this property. Please have a look at http://www.sqlskills.com/blogs/paul/checkdb-from-every-angle-when-did-dbcc-checkdb-last-run-successfully/


Monday, June 03, 2013 - 3:31:58 PM - bass_player Back To Top

BTW, one way to check if it works in SQL Server 2000 is to run DBCC DBINFO () WITH TABLERESULTS on one of the databases using TSQL and check if the dbi_dbccLastKnownGood field exists. If it does, the script should work.


Monday, June 03, 2013 - 3:29:32 PM - bass_player Back To Top

Hi Ray,

I haven't tested this on SQL Server 2000 yet. I'll spin up a test environment to verify. However, with it failing on some SQL Server 2008 databases, I'm guessing it may have something to do with the databases being offline. You can add a filter on the list of databases and only run the script on the ones that are online

foreach($db in $serverInstance.Databases | Where-Object {($_.Name -ne "tempdb") -and ($_.Status -eq "Online")})


Monday, June 03, 2013 - 3:12:37 PM - Ray Back To Top

The code presented works fine on several of my servers but I receive the error below for each database on several others.

It fails on a Windows 2000/SQL 2000 server.  Works on several Windows 2003/SQL 2005 servers, and fails on several Windows 2008/SQL2008 servers.  The DBCC command works fine from SSMS.

The last run date for many (not all) of the databases is 1900-01-01 00:00:00.000

Here is the script I am running.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")

$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "MyServer\MyInstance"

foreach($db in $serverInstance.Databases)
{
    $lastDBCC_CheckDB = $db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] `
    | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} `
    | Select-Object $db.Name, Value
    $DaysOld = ((Get-Date) - [DateTime]$lastDBCC_CheckDB.Value).Days
    If ($daysOld -gt 7)
    {
        Write-Host $db.Name " Last DBCC CheckDB execution : " $lastDBCC_CheckDB.Value
    }
}

Here is a portion of the output

GAC    Version        Location                                                                                                                                            
---    -------        --------                                                                                                                                            
True   v2.0.50727     C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll                                         
Cannot convert null to type "System.DateTime".
At line:11 char:58
+     $DaysOld = ((Get-Date) - [DateTime]$lastDBCC_CheckDB. <<<< Value).Days
    + CategoryInfo          : NotSpecified: (:) [], RuntimeException
    + FullyQualifiedErrorId : RuntimeException


Monday, May 13, 2013 - 4:46:29 PM - Ed Watson Back To Top

Now that worked


Monday, May 13, 2013 - 11:47:37 AM - bass_player Back To Top

Ed,

Try to run the code one line at a time from within the Windows PowerShell console. For example

  Line 1

 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")
Enter
Line 2
$serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "YourSQL ServerInstanceName"
Enter
Line 3
$db = $serverInstance.Databases["name of one of your database"]
Enter
Line 4
$db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object $db.Name, Value

Better yet, use the Windows PowerShell ISE to run the code for better troubleshooting experience. I think that the code you managed to grab from the page isn't properly formatted

 


Monday, May 13, 2013 - 11:05:35 AM - Ed Watson Back To Top

I was able to grab it from the page source...and I get the following error...

 

Unexpected token 'serverInstance' in expression or statement.

At C:\Users\ewatson\AppData\Local\Temp\fd9be09c-2033-480e-b17c-80dc065d3fe8.ps1:1 char:93

+ [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") $serverInstance <<<< = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "sqlprod40

" $db = $serverInstance.Databases["NSS"] $db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Sele

ct-Object $db.Name, Value

+ CategoryInfo : ParserError: (serverInstance:String) [], ParseException

+ FullyQualifiedErrorId : UnexpectedToken


Monday, May 13, 2013 - 10:56:16 AM - Ed Watson Back To Top

I am sorry, but your comment is cut off.


Tuesday, May 07, 2013 - 1:26:55 PM - bass_player Back To Top
Ed,
Try this for one of your databases. Let's see what this code returns just for one of your databases 
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") $serverInstance = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "localhost" $db = $serverInstance.Databases["name of one of your database"] $db.ExecuteWithResults("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object $db.Name, Value

Monday, May 06, 2013 - 9:10:11 AM - Ed Watson Back To Top

Thanks for your reply, but it returns that message for each database on the server.


Friday, May 03, 2013 - 6:56:21 PM - bass_player Back To Top

Hi Ed,

From the error message, I'm guessing that you have some databases in offline mode. You can use the Status property of the Database class to filter against databases that are offline. 


Thursday, May 02, 2013 - 6:01:31 PM - Ed Watson Back To Top

I am getting the following errors for each DB on the server...

 

 

Exception calling "ExecuteWithResults" with "1" argument(s): "Execute with results failed for Database 'EdgeSight'. "
At C:\Users\ewatson\AppData\Local\Temp\491f9749-585c-4b5a-b47f-5eb5a154a76c.ps1:5 char:41
+ $lastDBCC_CHECKDB=$db.ExecuteWithResults <<<< ("DBCC DBINFO () WITH TABLERESULTS").Tables[0] | where {$_.Field.ToString() -eq "dbi_dbccLastKnownGood"} | Select $db.Name
, Value
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : DotNetMethodException
 
Cannot convert null to type "System.DateTime".
At C:\Users\ewatson\AppData\Local\Temp\491f9749-585c-4b5a-b47f-5eb5a154a76c.ps1:6 char:58
+     $DaysOld = ((Get-Date) - [DateTime]$lastDBCC_CHECKDB. <<<< Value).Days
    + CategoryInfo          : NotSpecified: (:) [], RuntimeException
    + FullyQualifiedErrorId : RuntimeException


Learn more about SQL Server tools