Potential numbering issue with Microsoft SQL Server 2012 +


#1

A potential issue has been identified and a resolution found:

Issue:
Workorder numbers suddenly jump / increment by 1000 and no one edited Global Settings to make it so, nor did anyone directly edit the database.

Cause:
SQL Express /SQL Server 2012 or newer has a new feature that attempts to improve performance by caching in advance autonumber values in groups of 1000 and holding them in memory to more quickly issue when a new record (such as a workorder) is created. As a side effect of this feature if the server restarts / crashes the cached numbers are lost and the next new workorder number jumps by 1000 or more.

Resolution:

  1. Make sure everything and everyone is out of AyaNova program and options.
  2. Backup your database (so that in the event the workorder numbers still increment incorrectly, you can just restore from backup and redo the steps again).
  3. Open SQLServer configuration manager on your server
  4. Select SQL Server 2012/2014 instance there right client and select Properties menu
  5. Select Startup Parameters tab from there and register -t272
  6. Restart the SQL Server 2012/2014 instance again
  7. On one station only, log into your AyaNova
  8. Create a new workorder, save and exit.
  9. Confirm that the workorder # increments correctly

DO refer to the attached screenshots

Step3_SQLConfigurationMgr
Step4_properties
Step5a_startupparameter
Step5b_startupparameter
Step6_restart