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:
Here is a script to run the stored procedure for all databases on a server:
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.