Sample Header Ad - 728x90

Identity value jumps when restarting SQL Server

7 votes
1 answer
11732 views
We just switched from SQL Server 2008 R2 to SQL Server 2012. I am facing a problem with identity columns: Whenever I restart SQL Server, the seed value for each identity column is increased by 1000 (for int identity columns it is 1000 and for bigint it is 10,000). For example, if the next int identity value for a table was 3, after restarting SQL Server it will be 1003. If I again restart SQL Server, it will be 2003 and so on. After searching Google, I found that it is a new feature (don't know what is use of it) in SQL Server 2012, having only two solutions if you want the old identity behaviour: 1. Use a sequence object This is not possible for me because: a) I am using the same database in SQL Server 2008 and 2012. I can't use sequence in 2008. b) If I go with sequence then I need to change the save procedure for each table, which would be a bulky task for us. 2. Use Trace Flag 272 (-T272) I can go with this solution because there is no need to make any changes to my application. Someone suggested adding -T272 as a start-up parameter, after this SQL Server identity will work as in previous versions. I did the same but it is not working. I don't want to make any changes to my database structure. Please suggest solutions or explain why -T272 is not working.
Asked by Alok Chandra Shahi (91 rep)
Jan 9, 2015, 01:30 PM
Last activity: Apr 25, 2024, 06:28 AM