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
Last activity: Apr 25, 2024, 06:28 AM