SQL Profiler Audit Logout duration 18446744073709548

Question

I'm running traces on our SQL Server 2014 box and see something disturbing. The duration column of the Audit Logout event is normally the time in microseconds a connection has been alive to the server and can get quite large due to connection pooling, but what does it mean when it's 18446744073709548?

I cant help to think this number is special. A bit like the value of maxint in .NET etc. I've googled this number and it returns various stuff. A few of them are about SQL server and the audit logout event, so I'm not the only one seeing this, but I cant find an explanation about what it means.

Anyone? (preferably someone who has participated in the development of SQL server ;))

br Frederic

Answer 1

I currently see the same duration for Audit logout, among other similar values. For the same SPID, I would expect this number to increase monotonously, but that is not the case. A few subsequent values:

• 0
• 3
• 6
• 2616
• 0
• 49530
• 196
• 18446744073709548
• 18446744073709545
• 18446744073709541
• 18446744073709548

(duplicate values omitted)

I did not arrive here by searching for that number, so presumably, these numbers occur frequently.

If it were a duration in ms, this would correspond to a duration of over 584942 years. The only machine I have heard of running for that long is Deep Thought, but that machine is definitely not hosting my SQL Server databases.

My assumption is that somehow, the durations of individual batches are subtracted from the cumulative duration, causing an unsigned long to wrap around. 2^64 = 18446744073709551616, which is very close to 1000 times the numbers we encounter. So, internally, the duration is probably stored in µs.

So, this seems a bug, but I do not think it will learn you anything about the performance of your batches.

I am running SQL Server 12.0 and profiler v17.9.1.