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