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.

Advertisements

3 thoughts on “Help, RecID is jumping

  1. Here as a script for copy-paste :
    USE [AXDBMS2009]
    GO
    /****** Object: Trigger [dbo].[RecIdJumpMonitor] Script Date: 10/21/2011 19:57:59 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    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
    BEGIN
    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’
    END

    Like

  2. 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

    Like

    • 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 🙂
      //kurt

      Like

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s