Monday, 14 April 2014

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.

3 comments:

  1. Hi - I am new Powershell language. can you tell excellently how to implement this change ?

    ReplyDelete

  2. here is the error i am facing
    PS C:\Windows\system32> Param
    The term 'Param' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spell
    ing of the name, or if a path was included, verify that the path is correct and try again.
    At line:1 char:6
    + Param <<<<
    + CategoryInfo : ObjectNotFound: (Param:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    PS C:\Windows\system32> (
    >> [Parameter(Mandatory=$false,Position=0)]
    >> [String]$sqlinstance_name = 'DMSQADB'
    >> )
    >>
    Missing closing ')' in expression.
    At line:3 char:5
    + <<<< [String]$sqlinstance_name = 'DMSQADB'
    + CategoryInfo : ParserError: (CloseParenToken:TokenId) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingEndParenthesisInExpression

    ReplyDelete

  3. here is the error i am facing
    PS C:\Windows\system32> Param
    The term 'Param' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spell
    ing of the name, or if a path was included, verify that the path is correct and try again.
    At line:1 char:6
    + Param <<<<
    + CategoryInfo : ObjectNotFound: (Param:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CommandNotFoundException

    PS C:\Windows\system32> (
    >> [Parameter(Mandatory=$false,Position=0)]
    >> [String]$sqlinstance_name = 'DMSQADB'
    >> )
    >>
    Missing closing ')' in expression.
    At line:3 char:5
    + <<<< [String]$sqlinstance_name = 'DMSQADB'
    + CategoryInfo : ParserError: (CloseParenToken:TokenId) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingEndParenthesisInExpression

    ReplyDelete