Every so often, usually in the middle of the night or on a holiday weekend, an identity column will hit the maximum size for it’s data type and stop allowing new values to be inserted into the table. It goes without saying that an identity column with enough activity to hit the maximum value of even a regular integer indicates a busy table. Oddly enough, none of the monitoring tools that I have looks for an identity column that is about to fill up.
Since I had identified a real problem I decided it was time to turn to my problem solver, SQL Server Management Studio. I was able to quickly throw together a stored procedure that I can install on each of my machines then add custom alerts to my monitoring tools to call it. Since some tools run at the server level and others at the database level I wrote the script to work at the database level. For simplicity I have included a script at the bottom to run this stored procedure on all databases on a server.
The stored procedure logic is to get all identity columns in a database, using the column type from sys.types to calculate percent full based on current identity value vs. maximum identity value for that type. The inner query then returns those values back to the outer query to be filtered and have severity assigned. I could have written the whole thing as a single select rather than nesting it but writing it this way made it so much more readable.
Here is the code for the stored procedure:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 | CREATE PROCEDURE dbo.sp_dba_check_identities_for_space @warning_threshold_in_pct int = 80, @error_threshold_in_pct int = 90 AS SELECT @warning_threshold_in_pct = @error_threshold_in_pct WHERE @warning_threshold_in_pct > @error_threshold_in_pct SELECT database_name, table_name, column_type, percent_used, CASE WHEN percent_used >= @error_threshold_in_pct THEN 'ERROR' ELSE 'WARNING' END AS severity FROM ( SELECT DB_NAME() as database_name, OBJECT_NAME(c.object_id) as table_name, IDENT_CURRENT(OBJECT_NAME(c.object_id)) as index_seed, t.name as column_type, (IDENT_CURRENT(OBJECT_NAME(c.object_id)) / CASE WHEN t.name = 'bigint' THEN 9223372036854775807.00 WHEN t.name = 'int' THEN 2147483647.00 WHEN t.name = 'smallint' THEN 32767.00 WHEN t.name = 'tinyint' THEN 255.00 WHEN t.name = 'numeric' THEN POWER(10, c.precision) - 1 WHEN t.name = 'decimal' THEN POWER(10, c.precision) - 1 WHEN t.name = 'money' THEN 922337203685477.5807 WHEN t.name = 'smallmoney' THEN 214748.3647 ELSE 1.00 END) * 100 AS percent_used FROM sys.columns c INNER JOIN sys.types t ON c.system_type_id = t.system_type_id WHERE c.is_identity = 1 AND OBJECTPROPERTY(c.object_id, 'IsUserTable') = 1 ) dt WHERE percent_used > @warning_threshold_in_pct GO EXEC sys.sp_MS_marksystemobject 'sp_dba_check_identities_for_space' |
Here is a script to run the stored procedure for all databases on a server:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | CREATE TABLE #sp_dba_check_identities_for_space_results ( database_name sysname, table_name sysname, column_type sysname, percent_used int, severity varchar(20) EXEC sp_MSforeachdb 'USE ? INSERT #sp_dba_check_identities_for_space_results EXEC dbo.sp_dba_check_identities_for_space' table_name, column_type, percent_used, severity DROP TABLE #sp_dba_check_identities_for_space_results |
Please let me know if you run into any issues, have any ideas that would make this stored procedure better or just want to share how you are using it. As always scripts from the internet are like Halloween candy, inspect before consumption. I offer no warranty beyond a sympathetic ear if you should run into any issues.
