Category: Administration

Josh Simar doesn’t like gaps in identity columns brought about by rollbacks:

At the end of this you can see that those records were in the table before the rollback as evidenced by the now 2000 records in the table. However we ran our identity checker after the rollback and we’ll see some interesting results because of that.

Checking identity information: current identity value '2000', current column value '1000'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

The rollback did absolutely nothing for our identity and left it as is.

The short answer is that this is expected and reasonable behavior. Don’t expect identity integers to be sequential; the best you’ll get (assuming no resets or overflows) is a guarantee that they’ll be monotonically increasing. I left the long answer as a comment, currently awaiting moderation.

Thomas Rushton warns us that undocumented commands in SQL Server are liable to change without notice:

I don’t have every version available to test, but it does appear that the record indicating Containment State is mis-spelled in SQL Server 2012 as “dbi_ContianmentState”, and then corrected in SQL 2014 and later. It’s a good job I’m not relying on it for anything.

So, yes, don’t rely on undocumented functions – as they may change without notice.

If there’s a documented method, use that one. If the only method available is undocumented, you can still use it, but be sure to test it with each release—that is, major release, service pack, or cumulative update.

Josh Darnell ran into a problem with a SQL Agent job:

One of my colleagues reached out to me recently about a production issue where a SQL Server Agent job had failed with this error message:

Msg 1105, Level 17, State 2, Line 15
Could not allocate space for object ‘dbo.SORT temporary run storage: 140737513062400’ in database ‘tempdb’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

I fully expected this to be a scheduled maintenance task, like index rebuilds or statistics updates. I’ve seen this error before in those contexts (rebuilding large indexes in tempdb, or updating statistics with FULLSCAN).

But watch as SQL Server subverts Josh’s expectations.