Job ID# sequence jumps after SQL Server 2012 restart

When using SQL Server 2012 as the CommServe SQL Instance host, the Job ID# may jump forward after restart of SQL Server services. This has no impact on CommServe operations.

Symptoms

After a restart or failover of SQL Server 2012 instance, the Job ID# jumps in value causing a gap in the Job ID# sequence.  Size of the gap may vary.

Causes

From Microsoft Development:

In SQL Server 2012 the implementation of the identity property has been changed to accommodate investments into other features. In previous versions of SQL Server the tracking of identity generation relied on transaction log records for each identity value generated. In SQL Server 2012 we generate identity values in batches and log only the max value of the batch. This reduces the amount and frequency of information written to the transaction log improving insert scalability.

Resolution

Gaps in the JOB ID# sequence has no impact on CommCell operations. However, if necessary, this identity generation feature can be disabled by using a Trace Flag (TF 272) which will provide the same behaviour as SQL Server 2008R2. Note that the performance of identity generation may be impacted by turning on this trace flag.

1. Open "SQL Server Configuration Manager"
   2. Click "SQL Server Services" on the left pane
   3. Right-click on your SQL Server instance name on the right pane
   4. Click "Properties"
   5. Click "Startup Parameters"
   6. On the "specify a startup parameter" textbox type "-t272"
   7. Click "Add"
   8. Confirm the changes

As documented in Microsoft's books online for previous versions of SQL Server the identity property does not guarantee the absence of gaps, this statement remains true for the above workaround.