Version 2

    DESCRIPTION

    This alert will calculate the ratio of current number of locks (counts rows in syslocks) against the max locks available (number of locks from sp_configure / sysconfigures).

     

    ALERT DEFINITION

    To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:

     

    Alert Name: Sybase Max Locks

     

    Execution Interval: 10 minutes

     

    Notification Text: The following servers are getting close to running out of locks

     

    SQL Statement:

    declare @MaxLocks int, @CurLocks int

    -- get the maximum value for locks

    select @MaxLocks=value from master.dbo.sysconfigures where name = 'number of locks'

     

    -- get the current number of locks

    select @CurLocks=count(1) from master.dbo.syslocks

     

    -- return the ratio as a percent

    select 100.0 * @CurLocks / @MaxLocks

     

    Execute Against: Monitored Instance

     

    Units: %

     

    High Threshold: Min 90, Max empty

    Medium Threshold: Min 80, Max 90