Help, RecID is jumping

At a customer site, we sometimes saw that the recid is “jumping” in Dynamics AX 2009. We cannot explain why, but it seems to happen a few times every year. To deal with this, we created the following SQL trigger :

Thanks to FSB-development that came up with this. I share it to the community, because it would be easy to modify for other purposes.

3 thoughts on “Help, RecID is jumping

  1. Hi Kurt,
    Having trigger on SystemSequences table won’t solve the issue. Moreover it is probably too late to turn trace on. To find source of these “jumps” I’d advise searching AOT for calls to reservation of RecId range like SystemSequence.suspendRecIds(Table.TableId) or direct update of SystemSequences table.

    Mykola Galak


    • Hy Mykola. Yes, the triger will not solve the issue, but I liked the idea to add triggers to spesific changes directly on the database, and I therefore wanted to share the ability 🙂


  2. Here as a script for copy-paste :
    USE [AXDBMS2009]
    /****** Object: Trigger [dbo].[RecIdJumpMonitor] Script Date: 10/21/2011 19:57:59 ******/
    ALTER TRIGGER [dbo].[RecIdJumpMonitor] ON [dbo].[SYSTEMSEQUENCES] after UPDATE AS

    DECLARE @oldVal bigint
    DECLARE @newVal bigint

    SET @oldVal = (SELECT nextval FROM deleted)
    SET @newVal = (SELECT nextval FROM inserted)

    IF @newVal-@oldVal >= 100000
    DECLARE @msg varchar(500)
    SET @msg = ‘RecId jumped with more than 100.000, please start AOS Tracing now.’
    –CHANGE THE VALUE FOR @recipients and @profile_name
    EXEC msdb.dbo.sp_send_dbmail @recipients=N’xxx@xxxx.xx’, @body= @msg, @subject = ‘SQL Server Trigger Mail’, @profile_name = ‘zulu’
    EXEC msdb.dbo.sp_send_dbmail @recipients=N’yyy@yyyyyy.yy’, @body= @msg, @subject = ‘SQL Server Trigger Mail At Customer XX, @profile_name = ‘zulu’


Leave a Reply to Kurt Hatlevik Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.