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