Using Non-Sequential Numbering to Prevent Locking In Your Dynamics 365 Business Central System
Running into locks in your system? Enable non-sequential numbering on your number series to prevent number series locking issues!
As of Business Central 2019 release Wave 2, users now have the option to enable non-sequential numbering on their number series records.
By selecting the Allow Gap in Nos. checkbox on the No. Series Lines page for a number series, you can enable automatic or manual assignment of numbers without requiring them to be sequential. That avoids performance issues that occur when the No. Series Line table is locked while the system creates and consumes a new number from a number series.
If you want to allow gaps in certain number series, you should first consult with your auditor or accounting manager to ensure that you adhere to the legal requirements in your country or region.
This feature is good for reducing hiccups in processing transactions. If you are an FDA-Regulated or publicly-traded company you can't afford to have gaps in your number series.
You can learn more about non-sequential numbering on the Microsoft blog.
Using Application Insights?
Application Insights is a diagnostic tool you can use to determine why your Dynamics 365 Business Central system is behaving a certain way. This can include gathering information on extensions, diagnosing a slow-down in processing, or giving you a view of the traffic and data running through your system.
In this scenario, you can use event ID RT0008 to view incoming web service telemetry data. Use the sample query below to see if your number series is causing your record locks. Using that, in conjunction with Application Insights, can identify the issue and a potential fix.
Sample query:
traces
| where timestamp > ago(60d)
| where customDimensions.aadTenantId == 'YOUR AAD ID HERE' and isnotempty( 'YOUR AAD ID HERE')
| where customDimensions.environmentName == 'YOUR ENVIRONMENT NAME HERE' and isnotempty( 'YOUR ENVIRONMENT NAME HERE')
| where customDimensions.eventId == 'RT0005'
| where customDimensions.alObjectName == 'NoSeriesManagement'
| where customDimensions.sqlStatement matches regex "No_ Series Line.+WITH\\(UPDLOCK\\)"
| extend alStackTrace = tostring(customDimensions.alStackTrace)
,companyName = tostring(customDimensions.companyName)
| extend noSeries = case (
alStackTrace matches regex 'Gen. Jnl.-Post Batch\\"\\(CodeUnit 13\\).CheckDocumentNo', 'Gen. Jnl.-Post Batch (CodeUnit 13)'
, alStackTrace matches regex 'Customer\\(Table 18\\).OnInsert', 'Customer (Table 18)'
, alStackTrace matches regex 'Customer\\(Table 18\\).AssistEdit', 'Customer (Table 18)'
, alStackTrace matches regex 'Vendor\\(Table 23\\).OnInsert', 'Vendor (Table 23)'
, alStackTrace matches regex 'Item\\(Table 27\\).OnInsert', 'Item (Table 27)'
, alStackTrace matches regex 'Sales Header\\"\\(Table 36\\).InitInsert', 'Sales Header (Table 36)'
, alStackTrace matches regex 'SalesHeader - OnAfterInitRecord', 'Sales Header (Table 36)'
, alStackTrace matches regex 'SalesHeader - OnBeforeInsertRecord', 'Sales Header (Table 36)'
, alStackTrace matches regex 'Sales Header\\"\\(Table 36\\).AssistEdit', 'Sales Header (Table 36)'
, alStackTrace matches regex 'Purch.-Post\\"\\(CodeUnit 90\\).UpdatePostingNos', 'Purch.-Post (CodeUnit 90)'
, alStackTrace matches regex 'Purchase Header\\"\\(Table 38\\).InitInsert', 'Purchase Header (Table 38)'
, alStackTrace matches regex 'Purchase Header\\"\\(Table 38\\).AssistEdit', 'Purchase Header (Table 38)'
, alStackTrace matches regex 'Requisition Line\\"\\(Table 246\\).SetReplenishmentSystemFromProdOrder', 'Requisition Line (Table 246)'
, alStackTrace matches regex 'Assembly Header\\"\\(Table 900\\).OnInsert', 'Assembly Header (Table 900)'
, alStackTrace matches regex 'Assembly Header\\"\\(Table 900\\).AssistEdit', 'Assembly Header (Table 900)'
, alStackTrace matches regex 'Copy Job\\"\\(Page 1040\\).ValidateUserInput', 'Copy Job (Page 1040)'
, alStackTrace matches regex 'Mini Customer Template\\"\\(Table 1300\\).InitCustomerNo', 'Mini Customer Template (Table 1300)'
, alStackTrace matches regex 'Item Template\\"\\(Table 1301\\).InitItemNo', 'Item Template (Table 1301)'
, alStackTrace matches regex 'Mini Vendor Template\\"\\(Table 1303\\).InitVendorNo', 'Mini Vendor Template (Table 1303)'
, alStackTrace matches regex 'CustCont-Update\\"\\(CodeUnit 5056\\).OnInsert', 'Contact (CodeUnit 5056)'
, alStackTrace matches regex 'CustCont-Update\\"\\(CodeUnit 5056\\).InsertNewContact', 'Contact (CodeUnit 5056)'
, alStackTrace matches regex 'Contact\\(Table 5050\\).OnInsert', 'Contact (Table 5050)'
, alStackTrace matches regex 'Service Header\\"\\(Table 5900\\).OnInsert', 'Service Header (Table 5900)'
, alStackTrace matches regex 'Service Header\\"\\(Table 5900\\).AssistEdit', 'Service Header (Table 5900)'
, alStackTrace matches regex 'Production Order\\"\\(Table 5405\\).OnInsert', 'Production Order (Table 5405)'
, alStackTrace matches regex 'Item Tracking Lines\\"\\(Page 6510\\).AssignNewLotNo', 'Item Tracking Lot no (Page 6510)'
, alStackTrace matches regex 'Sales-Post\\"\\(CodeUnit 80\\).OnRun', 'Sales order posting (CodeUnit 80)'
, alStackTrace matches regex '\\(CodeUnit 91\\).OnAfterConfirmPost', 'Purchase order posting (CodeUnit 91)'
, alStackTrace matches regex '\\(CodeUnit 92\\).OnAfterConfirmPost', 'Purchase order posting (CodeUnit 92)'
, alStackTrace matches regex 'Transfer Header\\"\\(Table 5740\\).OnInsert', 'Transfer Header (Table 5740)'
, alStackTrace matches regex 'Whse.-Post Receipt\\"\\(CodeUnit 5760\\).Code', 'Whse.-Post Receipt (CodeUnit 5760)'
, alStackTrace matches regex 'Whse.-Post Shipment\\"\\(CodeUnit 5763\\).Code', 'Whse.-Post Shipment (CodeUnit 5763)'
, alStackTrace matches regex 'Warehouse Activity Header\\"\\(Table 5766\\).OnInsert', 'Warehouse Activity Header (Table 5766)'
, alStackTrace matches regex 'Process Proposal Lines\\"\\(CodeUnit 11000000\\).CreatePaymentHistory', 'Process Proposal Lines (CodeUnit 11000000)'
, alStackTrace matches regex 'Service Contract Header\\"\\(Table 5965\\).OnInsert', 'Service Contract Header (Table 5965)'
, alStackTrace matches regex 'Serv-Documents Mgt.\\"\\(CodeUnit 5988\\).SetNoSeries', 'Serv-Documents Mgt. (CodeUnit 5988)'
, alStackTrace matches regex 'Whse.-Activity-Register\\"\\(CodeUnit 7307\\).CheckLines', 'Whse.-Activity-Register (CodeUnit 7307)'
, alStackTrace matches regex 'Warehouse Receipt Header\\"\\(Table 7316\\).OnInsert', 'Warehouse Receipt Header (Table 7316)'
, alStackTrace matches regex 'Warehouse Receipt Header\\"\\(Table 7316\\).AssistEdit', 'Warehouse Receipt Header (Table 7316)'
, alStackTrace matches regex 'Warehouse Shipment Header\\"\\(Table 7320\\).OnInsert', 'Warehouse Shipment Header (Table 7320)'
, 'other' // these are likely from per-tenant extensions or app source apps
)
| summarize Count = count() by noSeries,companyName
| project
Count,
["Company Name"] = companyName,
["No. Series"] = noSeries
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.