Category: Administration

Michael J. Swart has a script to monitor identity columns:

My friend pointed out an interesting RCA by Github where a

database table’s auto-incrementing ID column exceeded [maxint]. When we attempted to insert larger integers into the column, the database rejected the value

This led to a discussion about setting up monitoring for this kind of problem in our software. We have a place for monitoring and health-checks for all our databases. We just need to know how to define them.

So how do I create a script that reports any tables whose current identity values are above a certain threshold? This is what I came up with. Maybe you’ll find it useful too.

Click through for the script, but also read the comments for a simplification. The next step in the process is to figure out which tables can handle it and auto-reseed when you get to a certain threshold. Most commonly, this would be queue tables, where the active set of IDs in use is a relatively small number and there are no ID stragglers hanging around, so resetting is safe.

Comments closed

Jignesh Raiyani explains the purpose of the identity cache and shows how you might disable it if you want to:

In the release of SQL Server 2012, Microsoft has introduced a feature of Identity Cache. The identity jump doesn’t cause any issue neither with the database nor the running tasks; however, this identity value gap is not acceptable in some of the business-oriented scenarios. This Identity counter values of the columns are stored in the system table separately, which is known as sys.identity_columns DMV. This reference object sys.identity_columns does not rely on the transaction status, and it doesn’t matter that the transaction on the user table is committed or rolled back. Now, in case this user transaction is being rolled back, we will find that the identity value has been skipped in the sys.identity_columns table. The reason being is that the value of the Identity column value does not get rolled back. Apart from the above scenario, there could be various reasons or situations where these issues of the skip of the Identity value will be seen, which will be small in number and random. However, when there is an issue of Identity value jump, the amount of the skip value will be prominent and precise in number, depending upon the column data type, which is easy to identify.

My advice is, don’t disable this unless you have a really good reason to. “I don’t want to see numbers skipped” is a bad reason because identity columns never guarantee sequential insertion. You can still have skips due to transaction rollbacks or other errors on insertion, and the identity cache helps with performance problems in cases with lots of writers inserting rows into the table.

Comments closed

Eric Cobb notices something strange:

When the new log files were added, SQL Server immediately filled them to match the fullness percentage of the original log files. As I manually increased the size of the log file, SQL Server again shifted the transactions around to keep both log files at the exact same fullness percentage. So, if the first log was 95% full, the second log was 95% full, regardless of the actual log file sizes. If the first log was 80% full, so was the second. The more I expanded the second log, the more transactions SQL Server would move to it, always keeping the fullness percentage the same on both log files. The larger the second log became, the more space it freed up on the first log, but the 2 logs were always exactly in sync in terms of fullness.

This is some interesting behavior, especially because transaction log files don’t use proportional fill.

Comments closed