locked
SQL Server: On the upper limit of the event sequence ID in the trace log RRS feed

  • Question

  • I'm thinking to output the trace log of the SQL server referring to the following URL, but I was concerned with one thing.

    https://msdn.microsoft.com/ja-jp/library/ms190362(v=sql.120).aspx

    https://technet.microsoft.com/ja-jp/library/ms190762(v=sql.105).aspx

    There is an Event Sequence as "increasing unique value", but where will it be recycled?

    I want to know the upper limit of it.
    Wednesday, December 6, 2017 7:01 AM

Answers

  • Hi ヴィタミンウォーター,

    The value upper limit of this column comes from my test, there may be an error occurred during my test. Please use the value given from document, it is int.

    The ""EventSequence" recycle from negative values" does not mean that it starts from -9,223,372,036,854,775,808, it means a cycle, the value will increase from 0 to 2147483647, then it will decrease from 2147483647 to 0.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 7:56 AM

All replies

  • Hi ヴィタミンウォーター,

    SQL Server uses a data type Bigint to store this column, that is to say the upper limit of this column is 2^64. 

    Besides, when this column is exceeded and the Event Sequence becomes negative. The SQL Server is using an interlocked instruction to increment the value so the event sequence counts down negatively until wrap around to 0 occurs.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 7, 2017 6:12 AM
  • Thank you for answer.

    I have forgotten to write, but I have been getting "Audit login event class" and "Audit login failed event class" information as a trace log.
    And because of below pages, I was thinking "EventSequence" is int type and upper limit is 2147483647.
    But it's wrong?

    https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/audit-login-event-class
    https://docs.microsoft.com/en-us/sql/relational-databases/event-classes/audit-login-failed-event-class
    https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql

    Also, after exceeding the upper limit, "EventSequence" recycle from negative values instead of 0?
    If "EventSequence" is bigint type, it starts from "-9,223,372,036,854,775,808"?
    Is there a way to start from 0 when the upper limit is exceeded in trace log?
    Thursday, December 14, 2017 7:25 AM
  • Hi ヴィタミンウォーター,

    The value upper limit of this column comes from my test, there may be an error occurred during my test. Please use the value given from document, it is int.

    The ""EventSequence" recycle from negative values" does not mean that it starts from -9,223,372,036,854,775,808, it means a cycle, the value will increase from 0 to 2147483647, then it will decrease from 2147483647 to 0.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, December 14, 2017 7:56 AM
  • Thank you very much!
    Teige!

    Because it was polite answer, I totally understood about EventSequence.
    It was very helpfull.
    Thursday, December 14, 2017 8:14 AM