We all try hard to plan for data growth and changing workloads as we build and adapt our SQL Server systems and to anticipate potential problems. Some issues, however, take a long time to appear. They lay in wait, unnoticed but not dormant, and if they strike without warning, they can cause severe disruption and extended downtime.
In November 2018, Basecamp, a software as a service provider, was caught off guard when one of their events tables ran out of IDENTITY values. It put their service into read-only mode for almost 5 hours. If you detect this looming problem early enough, you’ll have the time you need to plan and implement a cure, such as changing the datatype of the IDENTITY
column from an INT
to a BIGINT
, as described by Danny Kruge in his Simple-Talk article. However, this can take time and planning, and may also require a scheduled maintenance window.