One fine day, there was panic in the department when I reached office. All the ETL jobs had failed and the data warehouse was not refreshed overnight. Looking at the logs, the support team told that the max value of the integer identity column has been reached in one of the main fact tables and hence the failure. Lots of solutions were proposed that would result in downtime of minimum of 6 hours - the fact table in that system had 250 million records.
I then suggested a simple solution that could be implemented in a second and the systems could be up and running from the next minute. The solution was to 'Reseed' the identity column to minimum negative value allowed for integer value.
DBCC CHECKIDENT (MyTable, reseed, -2,147,483,648)
This was implemented and the jobs ran fine. The key to this solution is the initial seed value of the integer column in the table - if it had started with the above negative value, this would not have been possible.
No comments:
Post a Comment