PowerSQL – Identify a Head of the Database Blocks and Generate Automated Alert across all listed SQL Instances

This is going to be a simplest way of capturing and sending some useful information via an email of ‘N’ number of servers. With a little modification, you can implement various database related automation and capture very useful information.

To Identify the SPID (Session ID) of a head of the blocking chain using SMO server object using method named EnumProcesses which lists all running processes in an instance. Once the SMO server object is instantiated, all you need to invoke is the EnumProcesses method.

Please refer by earlier post to load SMO’s, if it’s not loaded.

http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/2013/05/01/powersql-list-sql-server-instance-configuration-details-and-sending-an-automated-email/

I’ve also used global variables for email sending option. First lets create a text file List.txt in which all instance names are stored and Change the MailServer, EmLst Parameters as per your configuration. Schedule a sql job to run it periodically so that an auto-generated alerts will be sent an intended administrators.

DBBlocks.ps1 – Copy and paste below content

************************************************************************

$MailServer=”aqmail01.PowerSQL.com”

$Emlst=”powersql@powersql.com”

$Dbblock = “dbblock.htm”

New-Item -ItemType file $Dbblock -Force

Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
Add-Content $fileName “<html>”
Add-Content $fileName “<head>”
Add-Content $fileName “<meta http-equiv=’Content-Type’ content=’text/html; charset=iso-8859-1′>”
Add-Content $fileName ‘<title>Database Blocks Report</title>’
add-content $fileName ‘<STYLE TYPE=”text/css”>’
add-content $fileName “<!–”
add-content $fileName “td {”
add-content $fileName “font-family: Tahoma;”
add-content $fileName “font-size: 11px;”
add-content $fileName “border-top: 1px solid #999999;”
add-content $fileName “border-right: 1px solid #999999;”
add-content $fileName “border-bottom: 1px solid #999999;”
add-content $fileName “border-left: 1px solid #999999;”
add-content $fileName “padding-top: 0px;”
add-content $fileName “padding-right: 0px;”
add-content $fileName “padding-bottom: 0px;”
add-content $fileName “padding-left: 0px;”
add-content $fileName “}”
add-content $fileName “body {”
add-content $fileName “margin-left: 5px;”
add-content $fileName “margin-top: 5px;”
add-content $fileName “margin-right: 0px;”
add-content $fileName “margin-bottom: 10px;”
add-content $fileName “”
add-content $fileName “table {”
add-content $fileName “border: thin solid #000000;”
add-content $fileName “}”
add-content $fileName “–>”
add-content $fileName “</style>”
Add-Content $fileName “</head>”
Add-Content $fileName “<body>”
add-content $fileName “<table width=’100%’>”
add-content $fileName “<tr bgcolor=’#CCCCCC’>”
add-content $fileName “<td colspan=’7′ height=’25’ align=’center’>”
add-content $fileName “<font face=’tahoma’ color=’#003399′ size=’4′><strong>Database Blocks Report – $date</strong></font>”
add-content $fileName “</td>”
add-content $fileName “</tr>”
add-content $fileName “</table>”

}

# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)

Add-Content $fileName “<tr bgcolor=#CCCCCC>”
Add-Content $fileName “<td width=’10%’ align=’center’>SERVER</td>”
Add-Content $fileName “<td width=’50%’ align=’center’>SPID</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>BLOCKING SPID</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>DATABASE</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>COMMAND</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>STATUS</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>LOGIN</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>HOSTNAME</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>CPU</td>”
Add-Content $fileName “<td width=’10%’ align=’center’>MEMUSAGE</td>”
Add-Content $fileName “</tr>”
}

Function writeHtmlFooter
{
param($fileName)

Add-Content $fileName “</body>”
Add-Content $fileName “</html>”
}

Function writeDBInfo
{
param($fileName,$server,$Spid,$BlockingSpid,$Database,$Command,$Status,$Login,$hst,$CPU,$Memusage)

Add-Content $fileName “<tr>”
Add-Content $fileName “<td>$Server</td>”
Add-Content $fileName “<td>$SPID</td>”
Add-Content $fileName “<td>$BLOCKINGSPID</td>”
Add-Content $fileName “<td>$DATABASE</td>”
Add-Content $fileName “<td>$COMMAND</td>”
Add-Content $fileName “<td>$Status</td>”
Add-Content $fileName “<td>$Login</td>”
Add-Content $fileName “<td>$hst</td>”
Add-Content $fileName “<td>$CPU</td>”
Add-Content $fileName “<td>$Memusage</td>”
#<td bgcolor=’#FF0000′ align=center>
Add-Content $fileName “</tr>”
}

Function sendEmail

{
param($from,$to,$subject,$smtphost,$htmlFileName)
[string]$receipients=”$to”
$body = Get-Content $htmlFileName
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
}

writeHtmlHeader $Dbblock

#Use of Global Variable

$global:a=0

function increment {
$global:a++
}

foreach ($server in Get-Content List.txt)
{
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server
$blocks=$server.EnumProcesses() |Where {$_.BlockingSpid -ne 0 -and $_.spid -gt 50 }

if ($blocks -ne $NULL)
{
foreach ($db in $blocks)
{
Add-Content $Dbblock “<table width=’100%’><tbody>”
Add-Content $Dbblock “<tr bgcolor=’#CCCCCC’>”
Add-Content $Dbblock “<td width=’100%’ align=’center’ colSpan=10><font face=’tahoma’ color=’#003399′ size=’2′><strong> $server </strong></font></td>”
Add-Content $Dbblock “</tr>”
writeTableHeader $Dbblock
increment $global:a
write-host $Dbblock $server $db.Spid $db.BlockingSpid $db.Database $db.Command $db.Status $db.Login $db.host $db.cpu $db.Memusage
writeDBInfo $Dbblock $server $db.Spid $db.BlockingSpid $db.Database $db.Command $db.Status $db.Login $db.host $db.cpu $db.Memusage
}
}

Add-Content $Dbblock “</table>”
}

writeHtmlFooter $Dbblock

if ($global:a -ge 1)
{
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
sendEmail powersql@powersql.com “$emlst” “Database Blocks Space Report – $Date” $MailServer $Dbblock
}

How to run the above file

– You can right click and execute

– Goto to PowerShell Console – .\DBBlocks.PS1

Output:-

second

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL and tagged , , . Bookmark the permalink.

One Response to PowerSQL – Identify a Head of the Database Blocks and Generate Automated Alert across all listed SQL Instances

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s