Alert Mechanism

  • I need to configure the below alerts manually for multiple servers

    Backup failure, disk space ,  long running queries, deadlock , memory utilisation >80,  primary datafile  growth etc,

    any scripts is helpful

  • If you want all of that, you should buy a monitoring product where you don't have to worry about what the code actually does.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • ramyours2003 wrote:

    I need to configure the below alerts manually for multiple servers

    Backup failure, disk space ,  long running queries, deadlock , memory utilisation >80,  primary datafile  growth etc,

    any scripts is helpful

    It would be a rare case where your memory utilization would be less than 80%.

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Of course, a monitoring product is nice and fine and $$

    But, if you are on a 0 budget, you could start the KISS way:

    ( we always install as DBA DBMail profile )

    function Add-SQLDBADefaultAlerts {
    <#
    .SYNOPSIS
    Add default SQL Server system alerts

    .DESCRIPTION
    set up Alerts for Severities 17–25
    Configure SQL Server to forward instances of:
    - Alerts for Severities 17–25
    - Common IO errors (824, 825, 826)
    to a SQL Server Agent Operator.

    .PARAMETER <Parameter-Name>
    If bound parameters, no need to put them overhere

    .EXAMPLE
    Add-SQLDBADefaultAlerts -SQLInstance myserver\myinstance ;


    .NOTES
    -Date 2015-05-29 - Author Bijnens Johan

    .LINK
    http://sqlmag.com/blog/sql-server-database-corruption-part-v-storage-problem-alerts

    .LINK
    http://sqlmag.com/blog/alwayson-ags-and-sql-server-jobs-setting-failover-alerts

    .LINK
    http://technet.microsoft.com/en-us/library/hh847834.aspx

    #>
    [CmdletBinding()]
    Param([parameter(Mandatory=$true,HelpMessage="SQL Server Instance Name")][string]$SQLInstance
    )

    # Check module SQLPS
    if ( !(get-module -name SQLPs ) ) {

    # save original location
    Push-Location
    # SQLPs will set the current location to SQLSERVER:\ !!
    # -DisableNameChecking -> avoid remarks abount non-discouverable function names
    import-module -name SQLPs -DisableNameChecking | out-null

    #reset current location to original location
    Pop-Location

    }

    #Interrupt when errors occur
    Trap {
        # Handle the error
        $err = $_.Exception
    #Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
    $ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
    write-warning $('Trapped error at line [{0}] : [{1}]' -f $ErrorLineNumber, $err.Message );

        write-error $err.Message
        while( $err.InnerException ) {
            $err = $err.InnerException
            write-error $err.Message
            };
        # End the script.
        break
        }

    $OperatorName = 'DBAGroup' ;
    $EmailAddress = 'Mailbox.dba@Your.Company' ;

    $SQLSystemAlerts= @{};
    #-- Alert Names start with the name of the server
    $SQLSystemAlerts += @{ 17 = 'Alert - Sev 17 Error: Query Out of Resources' } ;
    $SQLSystemAlerts += @{ 18 = 'Alert - Sev 18 Error: DBengine error' } ;
    $SQLSystemAlerts += @{ 19 = 'Alert - Sev 19 Error: Fatal Error in Resource' } ;
    $SQLSystemAlerts += @{ 20 = 'Alert - Sev 20 Error: Fatal Error in Current Process' } ;
    $SQLSystemAlerts += @{ 21 = 'Alert - Sev 21 Error: Fatal Error in Database Process' } ;
    $SQLSystemAlerts += @{ 22 = 'Alert - Sev 22 Error Fatal Error: Table Integrity Suspect' } ;
    $SQLSystemAlerts += @{ 23 = 'Alert - Sev 23 Error: Fatal Error Database Integrity Suspect' } ;
    $SQLSystemAlerts += @{ 24 = 'Alert - Sev 24 Error: Fatal Hardware Error' } ;
    $SQLSystemAlerts += @{ 25 = 'Alert - Sev 25 Error: Fatal Error' } ;
    $SQLSystemAlerts += @{ 823 = 'Alert - Error: 823 - Read/Write Failure' } ;
    $SQLSystemAlerts += @{ 824 = 'Alert - Error: 824 - Page Error' } ;
    $SQLSystemAlerts += @{ 825 = 'Alert - Error: 825 - Read-Retry Required' } ;


    try {
    $db = Get-SqlDatabase -ServerInstance $SQLInstance -Name msdb -ErrorAction Stop ;

    if ( $db.Parent.Edition -like '*express*' ) {
    # SQL Express doesn't support SQLAgent -> bail out
    throw $('[{0}] - Express edition not supported' -f $SQLInstance ) ;
    }


    # Select SQLAgent
    $SQLAgent = $db.parent.JobServer ;

    # Check if SQLAgent has been configured to use DBMail
    if ( $SQLAgent.DatabaseMailProfile -eq '' ) {
    Write-Warning 'Setting SQLAgent DatabaseMailProfile' ;
    $SQLAgent.DatabaseMailProfile = 'DBA' ;

    $SQLAgent.Alter();

    $SQLAgent.refresh();
    }
    else {
    Write-verbose 'Setting SQLAgent DatabaseMailProfile already configured' ;
    }



    $Operator = $SQLAgent.Operators | where name -eq $OperatorName

    #Check if operator needs to be created
    if ( !( $Operator ) ) {
    $Operator = New-Object Microsoft.SqlServer.Management.Smo.Agent.Operator $SQLAgent, $OperatorName
    $Operator.EmailAddress = $EmailAddress ;
    $Operator.Enabled = $true;

    # actually create the operator on the instance;
    $Operator.Create() ;

    Write-Warning $('SQLAgent Operator [{0}] created at [{1}].' -f $OperatorName, $db.Parent.DomainInstanceName );
    }


    $xCtr = 0 ;
    foreach ($k in ($SQLSystemAlerts.keys.GetEnumerator())) {
    $xCtr ++ ;
    $pct = $xCtr * 100 / $SQLSystemAlerts.count;
    Write-Progress -Activity $( 'Progressing [{0}] - [{1}]' -f $SQLInstance, $k ) -Status $SQLSystemAlerts.$k -PercentComplete $pct ;
    $TargetAlert = $('{0} - {1}' -f $db.Parent.DomainInstanceName , $SQLSystemAlerts.$k ) ;
    if ( !( $SQLAgent.Alerts | where Name -eq $TargetAlert )) {
    $a = New-Object Microsoft.SqlServer.Management.Smo.Agent.Alert $SQLAgent, $TargetAlert ;
    # no added value
    #$a.CategoryName = $AlertCategory ;
    if ( $k -lt 26 ) {
    $a.Severity = $k ;

    }
    else {
    $a.MessageID = $k ;
    }

    $a.IncludeEventDescription = [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail ;

    # First create the alert then add the operator and alter
    $a.Create() ;

    $a.AddNotification( $OperatorName, [Microsoft.SqlServer.Management.Smo.Agent.NotifyMethods]::NotifyEmail);
    $a.Alter() ;

    Write-Warning $('Added [{0}] ' -f $TargetAlert );

    }
    }

    #Close connection
    $db.Parent.ConnectionContext.Disconnect();

    }
    catch {

    # Handle the error
    $err = $_.Exception
    #Want to save tons of time debugging a #Powershell script? Put this in your catch blocks:
    $ErrorLineNumber = $_.InvocationInfo.ScriptLineNumber
    write-warning $('Trapped error at line [{0}] : [{1}]' -f $ErrorLineNumber, $err.Message );

    write-Error $err.Message
    while( $err.InnerException ) {
    $err = $err.InnerException
    write-error $err.Message
    };

    }

    }

    # clear-host
    Add-SQLDBADefaultAlerts -SQLInstance $TargetSQLInstance ;

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply