Monitor and Alert SQL Stack dumps

As many of know not all stack dumps raise an error in SQL Server and may go unnoticed for days, SQL stack dumps can sometimes cause database corruption and its always better to analyse the server immediately after a stack dump. Since there are no errors raised we cannot configure normal SQL Alert to report the SQL Stack dumps, I created a PowerShell script which can also be scheduled as a SQL Agent job to monitor the SQL Stack dumps, I have used Chad Millers invoke-sqlcmd2 just for flexibility in the future, you can use native invoke-sqlcmd without any issue. 

If you want to schedule this script as SQL Agent job remember to comment the clear-host and the invoke-sqlcmd2 function in the below script and try to use invoke-sqlcmd instead.Also I have scripted this to check the stack dumps for last one hour as I am running the SQL agent every hour in my environment, please change accordingly.

	# Script to Monitor Stack Dump in SQL Server
	# Created by - Vinoth N Manoharan
	# Version 1.0
	# Date - 17/02/2014
	Param(
	[Parameter(Mandatory=$false,Position=0)]
	[String]$sqlinstance_name = 'sqlinstancename'
	)
	##################################################Function Invoke-sqcmd##########################################################################
	#Script refered from http://gallery.technet.microsoft.com/scriptcenter/7985b7ef-ed89-4dfd-b02a-433cc4e30894
	<#
	.SYNOPSIS
	Runs a T-SQL script.
	.DESCRIPTION
	Runs a T-SQL script. Invoke-Sqlcmd2 only returns message output, such as the output of PRINT statements when -verbose parameter is specified
	.INPUTS
	None
	You cannot pipe objects to Invoke-Sqlcmd2
	.OUTPUTS
	System.Data.DataTable
	.EXAMPLE
	Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "SELECT login_time AS 'StartTime' FROM sysprocesses WHERE spid = 1"
	This example connects to a named instance of the Database Engine on a computer and runs a basic T-SQL query.
	StartTime
	-----------
	2010-08-12 21:21:03.593
	.EXAMPLE
	Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -InputFile "C:\MyFolder\tsqlscript.sql" | Out-File -filePath "C:\MyFolder\tsqlscript.rpt"
	This example reads a file containing T-SQL statements, runs the file, and writes the output to another file.
	.EXAMPLE
	Invoke-Sqlcmd2 -ServerInstance "MyComputer\MyInstance" -Query "PRINT 'hello world'" -Verbose
	This example uses the PowerShell -Verbose parameter to return the message output of the PRINT command.
	VERBOSE: hello world
	.NOTES
	Version History
	v1.0 - Chad Miller - Initial release
	v1.1 - Chad Miller - Fixed Issue with connection closing
	v1.2 - Chad Miller - Added inputfile, SQL auth support, connectiontimeout and output message handling. Updated help documentation
	v1.3 - Chad Miller - Added As parameter to control DataSet, DataTable or array of DataRow Output type
	#>
	function Invoke-Sqlcmd2
	{
	[CmdletBinding()]
	param(
	[Parameter(Position=0, Mandatory=$true)] [string]$ServerInstance,
	[Parameter(Position=1, Mandatory=$false)] [string]$Database,
	[Parameter(Position=2, Mandatory=$false)] [string]$Query,
	[Parameter(Position=3, Mandatory=$false)] [string]$Username,
	[Parameter(Position=4, Mandatory=$false)] [string]$Password,
	[Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=600,
	[Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15,
	[Parameter(Position=7, Mandatory=$false)] [ValidateScript({test-path $_})] [string]$InputFile,
	[Parameter(Position=8, Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
	)
	if ($InputFile)
	{
	$filePath = $(resolve-path $InputFile).path
	$Query = [System.IO.File]::ReadAllText("$filePath")
	}
	$conn=new-object System.Data.SqlClient.SQLConnection
	if ($Username)
	{ $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $ServerInstance,$Database,$Username,$Password,$ConnectionTimeout }
	else
	{ $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $ServerInstance,$Database,$ConnectionTimeout }
	$conn.ConnectionString=$ConnectionString
	#Following EventHandler is used for PRINT and RAISERROR T-SQL statements. Executed when -Verbose parameter specified by caller
	if ($PSBoundParameters.Verbose)
	{
	$conn.FireInfoMessageEventOnUserErrors=$true
	$handler = [System.Data.SqlClient.SqlInfoMessageEventHandler] {Write-Verbose "$($_)"}
	$conn.add_InfoMessage($handler)
	}
	$conn.Open()
	$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
	$cmd.CommandTimeout=$QueryTimeout
	$ds=New-Object system.Data.DataSet
	$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
	[void]$da.fill($ds)
	$conn.Close()
	switch ($As)
	{
	'DataSet' { Write-Output ($ds) }
	'DataTable' { Write-Output ($ds.Tables) }
	'DataRow' { Write-Output ($ds.Tables[0]) }
	}
	}
	##################################################END: Function Invoke-sqcmd##########################################################################
	Clear-Host
	$Errorlog = @()
	$Errorlog = Invoke-Sqlcmd2 -ServerInstance $sqlinstance_name -Database 'master' -Query 'sp_readerrorlog 1'
	ForEach($line in $Errorlog)
	{
	#$line.text
	if($line.text -like '*BEGIN STACK DUMP*')
	{
	if($line.LogDate -gt (Get-Date).AddHours(-1)) #Change According to your requirement
	{
	$mailsql = "EXEC msdb.dbo.sp_send_dbmail
	@recipients = 'abc@abc.com',
	@copy_recipients = 'abc@abc.com',
	@from_address = 'abc@abc.com',
	@body = 'SQLDumps has been reported in the last one hour for the SQL instance "+$sqlinstance_name+" Please Investigate-"+$line.logDate+":"+$line.text+"',
	@subject = '*******ERROR:SQL Dumps Reported********'"
	Invoke-Sqlcmd2 -ServerInstance $sqlinstance_name -Database 'msdb' -Query $mailsql
	}
	}
	}

view raw SQLTechnet - FindStackError.ps1 hosted with ❤ by GitHub