Profiler Shows Negative (Strange) EventSequence Values

I ran into an issue yesterday where the EventSequence column appears negative (or could be truncated and won't sort correctly) in the Profiler display.

Here is an example of the display from a trace that I debugged.

When I looked at the raw format I could see the storage for the EventSequence was 8 bytes (BIGINT) but the display was formatting an INT (4 bytes).

16332532102 __int64

                -847337082 int  

Event Data Column [51] = TRACE_COL_EVENTSEQ  

0x0000000102AF6C28 86 a9 7e cd 03 00 00 00 00  

                0xCD7EA986 = (int)0xCD7EA986 -847337082 int

WORKAROUND: Using the SQL Server function, fn_trace_gettable, you can see the correct BIGINT values. Also, the RML Utilities handle the values properly.

What I found is that the Profiler GUI uses definition files for how to format the column values. These files are located under: " Program Files (x86)\Microsoft SQL Server\100\Tools\Profiler\TraceDefinitions" and the EventSequence column is defined as an INT.

!!! CAUTION !!! - Changing these files is not supported and could cause additional display anomalies or even Profiler to terminate processing. !!!

Now I had to clear some of the cobwebs and think back a few years. When SQL 2005 shipped the EventSequence was an INT value but we updated it to a BIGINT during a service pack release and carried the BIGINT forward to the SQL 2008 code base as well. The definition files are correct when reading an old .TRC produced before these changes. For this specific trace the INT would have rolled over, making it hard to sort but it would not have truncated/ignored the additional 4 bytes from the BIGINT because the server did not produce a BIGINT. This is specifically why you can't just alter the definition file and have it assume it can access 8 bytes of data as only 4 may be stored.

For SQL Server 2008 traces they are always produced with 8 byte values (BIGINT) so I updated the .XML definition file to a type = 2. I knew I was opening a file with the correct storage and the display of the EventSequence was correctly displayed as a BIGINT and no longer truncated and formatted as an INT value.

It is unlikely that you will encounter this problem as trace has to be running for quite some time to produce more than a SINGED INTEGER value worth of events. The event sequence is kept from the start of the SQL Server process and incremented for each event that is produced. So you have to have trace running for quite some time to approach the INT_MIN value.

Bob Dorr - Principal SQL Server Escalation Engineer