Overview
FogBugz users may notice that on rare occasions case numbers "jump" (or increments) with 1000, or there might be gaps in case numbers.
Some users might have the impression, that cases are missing, but only case numbers were skipped.
For example, if the FogBugz case number is 105964, then the new case number starts from 106964. Seems like the system skipped or jumped 1000 case numbers.
Prerequisites
To apply any of the workarounds proposed in the Solution section you will need the following:
- Microsoft SQL Server Administrator role
- FogBugz Administrator role
Diagnosis
Case numbers (case IDs) in FogBugz need to be unique in order to ensure the unique identification of each case and all the related information (case history, sub-cases, etc.).
Case numbers are automatically generated, using Microsoft SQL Server Database's built-in auto-increment feature. This feature is used on the primary key ixBug in the Bug table which is storing the main data of the cases.
Since SQL Server version 2012 the database engine has a built-in caching mechanism intended to speed up data insert in tables using auto-increment columns. This feature is called Identity Cache and it works by reserving (caching) some IDs in advance for such columns. If a server restart occurs, to ensure uniqueness, the previously cached values are skipped, and the auto-increment continues with the next available number.
Solution
As explained in the diagnosis section, this is not an issue for FogBugz. FogBugz works correctly since case number uniqueness is still ensured. As such, we do not plan to provide an update to avoid the default database behavior.
If any of your processes or applications depend on the consecutiveness of FogBugz case numbers, we have a few possible workarounds and solutions.
The solutions listed in this article are applicable only for FogBugz On-Premise. When applying any of the solutions below, please consider their overall impacts and use them only when really necessary.
For FogBugz On-Demand no workaround is supported due to the nature of the hosting.
Disabling Identity Caching
To avoid gaps in the values of an identity column in cases where the server restarts unexpectedly or fails over to a secondary server you can disable identity caching.
Note that identity caching is used to improve INSERT performance on tables with identity columns. Disabling it will impact data INSERT performance.
Disabling Identity Caching at Server Level
You can disable Identity Caching at Server Level by applying the Trace Flag (TF) 272 using DBCC TRACEON - Trace Flags (Transact-SQL) command. This setting will apply to all databases on the given server.
Disabling Identity Caching at Database Level
Starting from Microsoft SQL Server 2017 you have the option to disable Identity Caching at database level by setting IDENTITY_CACHE = OFF using the ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL) command.
Reseeding the Bug Table on Server Startup
If you do not want to disable identity caching, the other option to avoid gaps in the values of case numbers is to reseed the ixBug key in the Bug table. This can be done using a SQL Server method or the FogBugz API.
Warning: Reseeding the Bug table will result in reusing the case numbers of deleted cases. The only time when FogBugz deletes cases is when you have a mailbox in Avatar -> Mailboxes configured to delete spam and inquiries after a certain age.
Reseed on Server Startup using SQL Server
You have the option to reseed the ixBug key in the Bug table on server startup by using a startup stored procedure that executes the DBCC CHECKIDENT (Transact-SQL) command with the RESEED option.
Reseed on Server Startup using FogBugz API
You have the option to reseed the ixBug key in the Bug table by using the FogBugz API command adminSetCaseNumber.
When using this method, ensure you choose the next biggest number as the parameter value for the ixBug . Otherwise, for example, due to a typo, you could result in a bigger "jump" (ex. instead of 1 000 you type 10 000).
Due to being more error-prone than previous methods, this method is recommended to be used as the last option.
Testing
Since the solutions are applicable only to FogBugz On-Premise, so is the testing of the solutions.
By applying any of the methods above for your FogBugz On-Premise instance, your case numbers should be continuously increasing, without "jumps". Please expect some gaps in case numbers in case you have enabled the automatic spam and inquiry removal feature of the Mailbox.