Monitoring of SQL Server sequence

Time:2021-3-3

We know that if the sequence numbers in the database are used up or exhausted due to improper type setting or business explosion and other reasons, an error like the following will occur:

 

Msg 11728, Level 16, State 1, Line 25

The sequence object ‘SEQ_OVER_TEST’ has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.

 

  If the DBA does not monitor the sequence, it is very likely that when the user reports / feedbacks this problem, it will find that the value of the sequence has been exhausted. At this time, the fire-fighting processing is a little hasty and awkward. Therefore, you should monitor the sequence usage of SQL server. For example, when the current value of the sequence reaches 85% of the maximum value, you should send an alarm notice. At this time, you may need to discuss the solution with the developers. The following script is used to obtain the usage of the sequence. You can use Python to collect the usage of each database sequence, and then trigger an alarm for the sequence whose current value exceeds 85% of the maximum value.

 

/**************************************************************************************************************
--Script name: DB_ sequences_ alert.sql
--Script Author: Xiaoxiang Hermit
--Created on: January 21, 2021
***************************************************************************************************************
Script function: check the usage of sequences in the database, if the current value of sequences exceeds maximum_ 85% of value,
We should take measures to deal with it.
***************************************************************************************************************
Note: none at the moment
***************************************************************************************************************
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sequences-transact-sql?view=sql-server-ver15
***************************************************************************************************************
Update record: create this script on 2021-01-21
***************************************************************************************************************/
 
SELECT @@SERVERNAME                     AS [server_name]
      ,DB_NAME(DB_ID())                 AS [database_name]
      ,SCHEMA_NAME(schema_id)           AS [scheme_name]
      ,name                             AS [sequence_name]
      ,create_date                      AS [create_date]
      ,modify_date                      AS [modify_date]
      ,type_desc                        AS [type_desc]
      ,start_value                      AS [start_value]
      ,current_value                    AS [current_value] 
      ,minimum_value                    AS [minimum_value]
      ,maximum_value                    AS [maximum_value]
      ,is_cycling                       AS [is_cycling]
      ,cache_size                       AS [cache_size] 
      ,is_exhausted                     AS [is_exhausted]
      ,CASE WHEN increment >=1  THEN 
            ROUND(CAST(current_value AS DOUBLE PRECISION)/CAST(maximum_value AS DOUBLE PRECISION)*100,2)
            ELSE 
            ROUND(CAST(current_value AS DOUBLE PRECISION)/CAST(minimum_value AS DOUBLE PRECISION)*100,2)
       END AS [percent_usage(%)]
FROM sys.sequences  
WHERE is_cycling =0
ORDER BY  [percent_usage(%)] DESC
GO