Eric M Russell (8/8/2014)

---

sql-lover (8/8/2014)

---

We're about to try trace flag T272 as a startup parameter on one of our SQL servers. This trace flag brings the Identity key generator to its previous SQL2008 behaviour, which was different.

I'll post back if this fixes the issue, makes it worse or has no effect.

Reading on this trace flag, it seems to disable a feature in 2012+ where gaps can result in identity incrmenting when the server is restarted. But that's doesn't seem to correspond to your specific issue of SQL Server seeming to reset identity seed backward during normal operation.

You may want to setup an event audit to determine if something like DBCC RESEED or "set identity_insert on" is being used.

http://msdn.microsoft.com/en-us/library/cc280663(v=sql.110).aspx

Also, confirm that only members of the DBA team have membership in a sysadmin or db_owner role.