Shulou(Shulou.com)05/31 Report--

How to monitor the sequence in SQL Server? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

We know that if the Sequence Numbers in the database is used up or exhausted due to various reasons such as improper type setting or business explosion, an error similar to 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 DBA does not monitor the sequence, it is very likely that when users report / report the problem, they will find that the value of the sequence has been exhausted, and the fire-fighting treatment will be a little hasty and embarrassing at this time. Therefore, the sequence usage of SQL Server should be monitored. For example, an alarm should be issued when the current value of the sequence reaches 85% of the maximum value. At this time, you may need to discuss the solution with the developer.

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 a sequence whose current value exceeds the maximum value of 85%.

/ *-- script name : db_sequences_alert.sql-script author: Xiaoxiang Hermit-creation date: 2021-01-21 * * * scripting function: check the usage of sequences in the database If the current value of sequences exceeds 85% of maximum_value, measures should be taken to deal with it. * points for attention : not yet * * * reference: 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) * 100Power2) ELSE ROUND (CAST (current_value AS DOUBLE PRECISION) / CAST (minimum_value AS DOUBLE PRECISION) * 100L2) END AS [percent_usage (%)] FROM sys.sequences WHERE is_cycling = 0 ORDER BY [percent_usage (%)] DESC GO about how to monitor sequences in SQL Server The answer to the question is shared here. I hope the above content can help you to a certain extent, if you still have a lot of doubts to be solved, you can follow the industry information channel to learn more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.