This isn't really a blog, its more of a holding page for my domain (seems a shame not to have a page), if I know you then add me on either LinkedIn or Facebook (links are on the right), however if I don't know you then I won't add you!

Thursday, 8 April 2021

Monitoring identity fields in SQL Server

 A common issue that I've encountered and certainly more than once, is where database inserts fail due to incorrectly sized identity fields. This is caused by the data type and its associated numerical limit being unable to store the next number in the identity sequence (known as the seed).

This is easily resolved by changing the fields data type, however this often means that users are unable to insert errors when this is discovered. Sizing all of your datatypes to be larger than necessary on the off chance of this issue is ill advised, as there are storage and performance issues with using the wrong datatype.

With this in mind I have created a simple script that identifies identity fields across a database, identifies the current seed and compares that against the associated limits based on the fields data type. This allows the monitoring of seed vs datatypes and more of a proactive approach taken to the problem.