Run Your SQL Server DBCCs and Check Your Errorlog with PowerShell

Frequently when I do a health check at a new client, I find that many, if not all databases, show that a DBCC CheckDB has not been run within the last two weeks. While one may think that SQL Server will automatically report every time one occurs, things happen, and you may not find out until it's too late. For this reason, it's wise to run this valuable check at least once a week on every database, if not more often. (If the databases are small enough, I like to run them once a night. It gives me more of an opportunity to recover with recent backups.)

Related: Getting SQL Server Performance Data with PowerShell

By now, you probably know that I've got PowerShell scripts for almost all my basic management tasks, and DBCC CheckDB is no exception. In Server Management Objects (SMO), the Database object has a method called CheckTables(). I use this with an argument of 'None,' and this has the equivalent of running DBCC CHECKDB WITH NO_INFOMSGS for each database. So, to connect to the instance and run CheckDB against all the databases on the instance, the code looks like this.

# Connect to the specified instance$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') $inst # Get the databases for the instance, and iterate through them$dbs = $s.Databasesforeach ($db in $dbs) {  # Check to make sure the database is not a system database, and is accessible  if ($db.IsSystemObject -ne $True -and $db.IsAccessible -eq $True) {    # Store the database name for reporting    $dbname = $db.Name        # Peform the database check    $db.CheckTables('None')        }  }

Now, I really like to look at the error log to see what the results are, before moving on, and sometimes there are lots of messages in the error log, making it difficult to find the results that are interesting to me. To address this, SMO has a method for the Server object called ReadErrorLog(). If you don't supply an argument, it'll return the contents of the current error log. The method returns a System.Data.DataRow collection, and this collection contains three properties—LogDate, ProcessInfo, and Text. The LogDate property contains the date and time the error message was written, the ProcessInfo tells us what SPID was involved, and the Text object contains the actual message.

PowerShell has a cmdlet that allows us to search the content of string objects for patterns called Select-String. To allow us to return all three properties while still selecting just those rows that are interesting to us, we need to concatenate the three properties into a single string object, and that becomes the value we specify to the -inputobject parameter to Select-String.

-inputobject {[string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text}

Since the results of DBCC are reported in the ErrorLog in the same line as the report that the DBCC was run, we can specify that we want just those lines that contain the string 'DBCC' using the argument "-context 0,0", and we specify the search string in the -pattern argument. Here's the code for returning the DBCC results for all DBCCs run on the instance since March 1, 2014.

$startdt = [datetime]'2014-03-01' # Get the current error log$err = $s.ReadErrorLog()$err | where {$_.LogDate -ge $startdt} | Select-String -inputobject {[string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text} -pattern 'DBCC' -context 0,0

Now, if I want to just pull back errors reported in the error log, I can look for the string 'Error:' in the pattern argument, but the important information about the error isn't in the same line as that string, it's in the line afterwards. To get that, I just change the context argument so I specify 0 lines before the found string and 1 line after the string, like this.

$err | where {$_.LogDate -ge $startdt} | Select-String -inputobject {[string] $_.LogDate + ' ' + $_.ProcessInfo + ' ' + $_.Text} -pattern 'Error:' -context 0,1

This provides a quick way for me to get the recent contents of the error log that are relevant, so I can act on problems quickly.

Related: Create Alert-Based Log Backups with PowerShell