Check DBCC CHECKDB Last Execution using PowerShell

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 theExecuteWithResults 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 Databaseclass 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 PowerShell script is shown below.

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
}

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
    }
}

 

Source collected from MSSQLTIPS.com