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 (

      $SqlInstance,

      $MailHost,

      $MailTo

)

function main {

      $verbosity = 2

      $ScriptName = $myInvocation.MyCommand.Name

      [void][reflection.assembly]::LoadWithPartialName("System.Data.SqlClient")

      $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

      $MasterConn.Open()

      $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)@host.com"

                        $MailBody = "DBCC reported errors. 

                        Instance: $SqlInstance

                        Database: $DatabaseName

                        $SqlCmdOutput

                        "

                        (new-object Net.Mail.SmtpClient($MailHost)).send($MailFrom,$MailTo,$MailSubject, $MailBody)

                 

                  }

            }

            $Rset.Close()

      }

      $MasterConn.Close()

}

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

      #

      #

      #usage:

      # 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.

      #param(

      #[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 ######

      param(

            [string] $incomingString,

            [int] $logLevel = 1 ,

            $ForegroundColor = $host.ui.RawUI.ForegroundColor,

            $BackgroundColor = $host.ui.RawUI.BackgroundColor,

            [switch]$NoNewLine

      )

      #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

}

main