Friday, June 15, 2012

Powershell to run a DBCC checkdb loop for all databases

I needed a loop that would run a dbcc check db on all online databases (other than tempdb).  I did this is sql first but I seemed to be getting strange errors.  After a bit of research, I saw that I wasn’t the only one and decided to drop out of sql and run the commands in as separated a manner as possible.  The script below gets all online databases and runs dbcc checkdb on all of them.  We use WITH NO_INFOMSGS so if we have any output, we consider it a problem and send it to an operator.  We use sqlcmd and a command processor so hopefully a single dbcc error will not corrupt the rest of the run...

We take 3 arguments
@SqlInstance – Name of sql instance to check
@MailHost – we need an SMTP server to send the message through
@MailTo – address of the operator that will get the error/warning messages

This was developed against SQL Server 2008 but I see no reason it wouldn't work on others...
Good luck

Update: 20120620 - added a kill all connections based on this post.  

param (
function main {
      $verbosity = 2
      $ScriptName = $myInvocation.MyCommand.Name
      $ConnString = "Server=$SqlInstance;Integrated Security=SSPI;Application Name=$ScriptName"
      $MasterConn = new-object ('System.Data.SqlClient.SqlConnection') $ConnString
      $MasterCmd = new-object System.Data.SqlClient.SqlCommand
      $MasterCmd.Connection = $MasterConn
      $SqlDBCC = "SELECT name AS name FROM master..sysdatabases WHERE Name <> 'tempdb'AND DATABASEPROPERTY(name, 'IsOffline') = 0"
      $MasterCmd.CommandText = $SqlDBCC
      $Rset = $MasterCmd.ExecuteReader()
      If ($Rset.HasRows -eq $true) {
            While ($Rset.Read()) {
                  $DatabaseName = $Rset["Name"]
                  out-log "Working with $DatabaseName.  Killing Connections at $(Date)"
                  $SqlCmdOutput = sqlcmd -E -S $SqlInstance -d $DatabaseName -Q "exec master.dbo.usp_KillConnections $DatabaseName"
                  out-log "$SqlCmdOutput
                  Starting Check"
                  $SqlCmdOutput = sqlcmd -E -S $SqlInstance -d $DatabaseName -Q "dbcc checkdb WITH NO_INFOMSGS"
                  if ($SqlCmdOutput -eq $Null) {
                        out-log "DBCC completed for $DatabaseName on $SqlInstance with no errors"
                  } else {
                        out-log "WARNING: DBCC Completed with errors:
                        $SqlCmdOutput" -ForeGroundColor Red
                        $MailSubject = "$DatabaseName @ $SqlInstance DBCC Failure"
                        $MailFrom = "DBCCRunner.$(hostname)"
                        $MailBody = "DBCC reported errors. 
                        Instance: $SqlInstance
                        Database: $DatabaseName
                        (new-object Net.Mail.SmtpClient($MailHost)).send($MailFrom,$MailTo,$MailSubject, $MailBody)

function Out-Log {
      #v.6 - ej 20120325 - Indented verbose output
      #v.5 - ej 20120210 - set logging to $env:HOMEDRIVE
      #v.4 - ej 20120207 - fixed tab issue that broke copy and paste to console.
      #v.3 - ej 20111109 - added newline and removed the timestamps from display
      #version .2 - ej, 20090114
      #this script will allow for logging and screen output based on a requested verbosity level.
      #taking a cue from syslog, we are defining 0 as most critical errors.
      #by default in this script, verbosity is set as 1.  so we only print items specifically marked as 0 criticality to screen
      #default items come in as a log level of 1, ie they are not printed.  both of these can be overridden
      # all items are logged to a file based on the scriptname and run date at c:\toolkit\scripts\logs
      # it is expected that the default log level will be used for warnings and some informational messages
      # debug messages will be given log levels of 2 and higher as detailed below.
      # critical messages should be marked w/ a log level of 0
      # in your script you can include the function with:
      # . c:\toolkit\scripts\out-log.ps1
      # you can then write all informational messages to log with
      # out-log <StringToLog> [LogLevel] [ForeGroundColor]
      #simplest case (when in doubt, use this)
      # out-log "message to log"
      #if you want to also ALWAYS print to screen you can set the message to level 0 with (use sparingly)
      # out-log "message to log" 0
      #if you want make it print in RED on the screen (dependent on whether it will print to screen)
      # out-log "message to log" 0 RED
      #to change the level of logging that you want to see on screen, you can set a global variable 'verbosity'
      #this script will print log levels that are equal to or less than the variable '$verbosity'.
      #a suggested usage is to accept a command line argument with verbosity level.
      #best way to accept verbosity via cmd line is to add a param to the beginning of your script. it needs to be first line.
      # if the param below is added to your script, you can add "-v 3" to your command line to set the verbosity to 3,
      # this script prints all log items with a lower loglevel than the chosen verbosity.
      # if not set, we assume verbosity 0.
      # we then expect that only critical errors would be sent to log level 0.  default log level is 1.
      #[string] $verbosity = 0
      # you can also set deeper log levels.  for example, if you want log level 1 to be warnings and information about script progress
      #    but you also want the option to enable/disable debug messages, you can log all your debug messages to a higher log
      # out-log "debug string to log" 2
      #if you then want to run your script such that you see debugging messages, you can run
      #./script.ps1 -verbosity 2
      #note that you can use shortened command line arguments.  you just need enough to be unique so unless you
      # define another param that starts w/ 'v', you can use
      #./script.ps1 -v 2
      ##### BEGIN SCRIPT ######
            [string] $incomingString,
            [int] $logLevel = 1 ,
            $ForegroundColor = $host.ui.RawUI.ForegroundColor,
            $BackgroundColor = $host.ui.RawUI.BackgroundColor,
      #we are defining the log directory on all machines to be, use trailing "\"
      $logDirectory = "$($env:HOMEDRIVE)\logs\"

      #set your date
      $logDate = Get-Date -Format yyyyMMdd-HHmmss

      #have we defined verbosity?
      #if verbosity is not defined, we set it here as 0
      if ($verbosity -eq $null) {$verbosity = 0}

      #have we defined the logfile?

      if (-not (test-path variable:outlogfile)) {
            #if no, create it now based on todays datetime and progname

            #check for the log path, create if not found
            if (!(Test-Path -path $LogDirectory)) {
                  $tmp = New-Item $LogDirectory -type directory
                  Write-Host " ---> Created Log Directory at " $LogDirectory

            #define name <ScriptName>-<Date>.log
            if($myInvocation.ScriptName -ne "") {
                  $scriptName = [IO.Path]::GetFileNameWithoutExtension($myInvocation.ScriptName)
            } else {
                  $scriptName = "NONAME"

            $logName = $logDirectory + $scriptName + "-" + $logDate + ".log"
            Write-Host "Logging to: $logname" -ForegroundColor DarkBlue -BackgroundColor DarkYellow
            Set-Variable -Name OutLogFile -Value $logName -Scope script

      #now we use the Logfile

      #if the log level is lower or equal to than the verbosity, we also spit to screen
      if ($logLevel -le $Verbosity) {
            Write-Host ("   " * $logLevel + $incomingString) -ForegroundColor $ForegroundColor -BackgroundColor $BackgroundColor -NoNewline:$NoNewLine

      #date time stamp for logging
      $stringToLog = $logDate + ": " + "   " * $logLevel + $incomingString

      #finally, stick this in the log
      Out-File -filepath $outLogfile -inputObject $stringToLog -append