Oh I see, thanks for the info. I will have to read up on XEvent as I haven't heard of it before.

Is there any kind of known bugs with the identity column in 2008r2? Yesterday we reached the 2.1 billion count limit (4 byte int) despite only having 20 million rows. Some gaps between identity values are over 1 million.

The only thing I've managed to find is a CACHE/NOCACHE option but it seems that wouldn't use such a large cache amount (and seem it only causes gaps on system crashes + MSSQL 11).

Yesterday I RESEEDED to the beginning of a 2.3 million range gap. Over the course of the day only 5,968 rows were inserted within that 2.3 million row range (it eventually exceeded the range -> duplicate issues). The server hasn't crashed or anything.

My assumption is an application bug but I was just checking that there isn't a 2008r2 bug related to sequences?

I found this forum post: http://social.msdn.microsoft.com/Forums/en-US/fd821aa1-f39a-4cfb-9843-effdadb3bd6f/identity-column-jumpednot-sequential-in-sql-2008-r2?forum=transactsql

Is this correct? I was under the assumption that an identity column was the same as an Oracle sequence i.e. incrementing number rather than "unique, not sequential". If not sequential I don't see why they would bother providing seed & increment options. I also don't see why SQL Server would "optimize" by jumping 40,000 numbers when less than 6000 rows were inserted over a day.

Edit: It is running SP1, the only "identi" related search of the SP2 bug fixes is "820859 - Fixes an issue in which an incorrect value is returned when you run SCOPE_IDENTITY(). "