{"name":"Identity columns near limit","description":"

This metric measures the number of identity columns that are near to the limit per database. If an identity column is getting close to the limit of the datatype, you need to know so that you can avoid logical problems in your application and SQL Server errors. For example, if you created an IDENTITY column of smallint datatype, if you try to insert more than 32767 rows in the table, you will get the following error:<\/p>\r\n

Server: Msg 8115, Level 16, State 1, Line 1<\/code><\/p>\r\n

Arithmetic overflow error converting IDENTITY to data type smallint. Arithmetic overflow occurred.<\/code><\/p>\r\n

This is a limitation of the datatype, not <\/em>the identity.<\/p>\r\n\r\n

For more information, see http:\/\/vyaskn.tripod.com\/sql_server_check_identity_columns.htm<\/a><\/p>\r\nIf you want to carry out further analysis, run this query to find which tables have the identity column near to the limit:<\/p>\r\n

WITH  CTE_1\r\n        AS (SELECT OBJECT_NAME(a.Object_id) AS table_name,\r\n                a.Name AS columnname,\r\n                CONVERT(BIGINT, ISNULL(a.last_value, 0)) AS last_value,\r\n                CASE WHEN b.name = 'tinyint' THEN 255\r\n                     WHEN b.name = 'smallint' THEN 32767\r\n                     WHEN b.name = 'int' THEN 2147483647\r\n                     WHEN b.name = 'bigint' THEN 9223372036854775807\r\n                END AS dt_value\r\n              FROM sys.identity_columns a \r\n              INNER JOIN sys.types AS b\r\n              ON\r\n                a.system_type_id = b.system_type_id\r\n           ),\r\n      CTE_2\r\n        AS (SELECT *,\r\n                CONVERT(NUMERIC(18, 2), ((CONVERT(FLOAT, last_value)\r\n                \/ CONVERT(FLOAT, dt_value)) * 100)) AS \"Percent\"\r\n              FROM CTE_1\r\n           )\r\n  SELECT *\r\n    FROM CTE_2\r\n    ORDER BY \"Percent\" DESC;<\/pre>","tsql":"WITH  CTE_1\r\n        AS (SELECT OBJECT_NAME(a.Object_id) AS table_name,\r\n                a.Name AS columnname,\r\n                CONVERT(BIGINT, ISNULL(a.last_value, 0)) AS last_value,\r\n                CASE WHEN b.name = 'tinyint' THEN 255\r\n                     WHEN b.name = 'smallint' THEN 32767\r\n                     WHEN b.name = 'int' THEN 2147483647\r\n                     WHEN b.name = 'bigint' THEN 9223372036854775807\r\n                END AS dt_value\r\n              FROM sys.identity_columns a \r\n              INNER JOIN sys.types AS b\r\n              ON\r\n                a.system_type_id = b.system_type_id\r\n           ),\r\n      CTE_2\r\n        AS (SELECT *,\r\n                CONVERT(NUMERIC(18, 2), ((CONVERT(FLOAT, last_value)\r\n                \/ CONVERT(FLOAT, dt_value)) * 100)) AS \"Percent\"\r\n              FROM CTE_1\r\n           )\r\n  SELECT COUNT(*) AS CNT\r\n    FROM CTE_2\r\n    WHERE \"Percent\" >= 90; -- 90%, threshold\r\n","instances":true,"frequency":86400,"databases":{"mode":1},"rateofchange":false,"metricenabled":true,"alertname":"Identity columns near limit","alertdescription":"
This alert is raised if an identity column is getting close to the limit of the datatype. You need to know about this so you can avoid logical problems in your application and SQL Server errors. For example, if you created an IDENTITY column of smallint datatype, if you try to insert more than 32767 rows in the table, you will get the following error:<\/p>\r\n
Server: Msg 8115, Level 16, State 1, Line 1<\/code><\/p>\r\n
Arithmetic overflow error converting IDENTITY to data type smallint. Arithmetic overflow occurred.<\/code><\/p>\r\n
This is a limitation of the datatype, not <\/em>the identity.<\/p>\r\n\r\n
 For more information, see http:\/\/vyaskn.tripod.com\/sql_server_check_identity_columns.htm<\/a><\/p>\r\nIf you want to carry out further analysis, run this query to find which tables have the identity column near to the limit:<\/p>\r\n
WITH  CTE_1\r\n        AS (SELECT OBJECT_NAME(a.Object_id) AS table_name,\r\n                a.Name AS columnname,\r\n                CONVERT(BIGINT, ISNULL(a.last_value, 0)) AS last_value,\r\n                CASE WHEN b.name = 'tinyint' THEN 255\r\n                     WHEN b.name = 'smallint' THEN 32767\r\n                     WHEN b.name = 'int' THEN 2147483647\r\n                     WHEN b.name = 'bigint' THEN 9223372036854775807\r\n                END AS dt_value\r\n              FROM sys.identity_columns a \r\n              INNER JOIN sys.types AS b\r\n              ON\r\n                a.system_type_id = b.system_type_id\r\n           ),\r\n      CTE_2\r\n        AS (SELECT *,\r\n                CONVERT(NUMERIC(18, 2), ((CONVERT(FLOAT, last_value)\r\n                \/ CONVERT(FLOAT, dt_value)) * 100)) AS \"Percent\"\r\n              FROM CTE_1\r\n           )\r\n  SELECT *\r\n    FROM CTE_2\r\n    ORDER BY \"Percent\" DESC;<\/pre>","aboveorbelow":"above","collections":1,"alertenabled":true,"_thresholds_high":{"selected":true,"value":10},"_thresholds_medium":{"selected":true,"value":5},"_thresholds_low":{"selected":true,"value":0},"thresholds_comment":"Note: These thresholds are intended as guideline values. If they seem too high or too low in your environment, replace them with values more suited to your server performance.","targetentitytype":0}