DBCC Checkdb on SQL Server Express Edition

Express edition is a really good database engine although it has its limits like 10GB max size, no AlwaysOn and other stuff. But what is really annoying is that SQL Server Agent is not there. How are we supposed to run backups, checkdb, index maintenance, update statistics and so on without it? Not to mention that the business will want to run SSIS jobs and/or simple T-SQL scripts?

To get around this limitation I created simple Powershell scripts for maintenance purposes, they can be run from Windows Task Scheduler. I’ll start with the DBCC CHECKDB jobs as they are very important and later upload scripts for the other maint tasks.

This is run on the SQL Server host and you need to define a catalog where you want to log the results.

It needs to be run as a sysadmin in SQL Server Express, that is, you need to know what to do if CHECKDB throws an error, most often it involves calling Microsoft as fast as you can, or just restoring a recent good backup and trying to puzzle together the missing data.

The script writes to Windows event log so you need to pick up errors from there, it also sends all failed tests to an email you set  in the function, via your smtp server which you also have to set.

It optionally sends successful checks, that is the $sendOnlyFailed parameter, set it to 0 and it will send an email everytime it has done a checkdb.

Now it has been tested but I may have changed something while writing this, maybe the server is full of gremlins or my texteditor has a bug in it, whatever the reason, test this thoroughly on your test servers before putting it on a production system, I will not take any responsibility if something bad happens. Your servers are your responsibility.

# Run checkdb on all databases
$ServerInstance = "localhost"
$DateTimestring = Get-Date -Format yyyyMMdd
$logFilePath = "W:\SQLBackup\scripts\CHECKDBOutput\"

$sendOnlyFailed = 1 # Set to 0 to send status for all checks

# Send status email
Function StatusEmail {
    Param([string]$mailstring)
    Send-MailMessage -SmtpServer "YOU SMTP SERVER" -From "SERVERNAME OR SIMILAR" -To @('LIST OF EMAIL ADRESSES') -Subject "DBCC CHECKDB" -Body $mailstring
}

$sqldatabases = "select name from sys.databases"
$dbs = (Invoke-Sqlcmd -Serverinstance $ServerInstance -Query $sqldatabases).name

foreach ($db in $dbs){
    $logFile = $logFilePath+"CHECKDB_Log_"+$db+"_"+$DateTimestring+".txt"
    $sqlcheckdb = "dbcc checkdb($db) with all_errormsgs"
    Invoke-Sqlcmd -Serverinstance $ServerInstance -Query $sqlcheckdb -QueryTimeout 0 -Verbose *> $logFile

    #Read logfile and if we do not have "CHECKDB found 0 allocation errors and 0 consistency errors in database" somewhere we have an error
    $searchForError = "CHECKDB found 0 allocation errors and 0 consistency errors in database"
    if(Select-String -Pattern $searchForError -Path $logFile) {
        $parameters = @{
        'LogName'  = 'Application'
        'EventId'  = 8957
        'EntryType'  = 'Information'
        'Source' = 'MSSQL$SQLEXPRESS'
        'Message'  = 'DBCC CheckDB success'
        }
        Write-EventLog @parameters
        if($sendOnlyFailed -eq 0) {
            StatusEmail "DBCC CHECKDB for database $db succeeded"
        }
    }else{
        $parameters = @{
        'LogName'  = 'Application'
        'EventId'  = 8957
        'EntryType'  = 'Error'
        'Source' = 'MSSQL$SQLEXPRESS'
        'Message'  = 'DBCC CheckDB failed, please check SQL error log'
        }
        Write-EventLog @parameters
        StatusEmail "DBCC CHECKDB for database $db failed, investigate!"
    }
}

# Delete old log files, older than 15 days
$limit = (Get-Date).AddDays(-15)
Get-ChildItem -Path $logFilePath -Recurse -Force | Where-Object { !$_.PSIsContainer -and $_.CreationTime -lt $limit } | Remove-Item 

 

Leave a Reply

Your email address will not be published. Required fields are marked *