Sql-server – SQL Server Trace Stopped Messages Displaying Random Login Name

extended-eventssql serversql-server-2012sql-server-2016trace

When traces are started and stopped we get messages similar to the following in the SQL Server error log:

SQL Trace ID 2 was started by login "DOMAIN\loginA".

and

SQL Trace stopped. Trace ID = '2'. Login Name = 'loginB'.

The problem I've noticed is that the loginB name doesn't always correspond to the loginA name that started the trace.

This has become a problem since we have started using Extended Events sessions more in our environments. We schedule traces to start and stop as the service account but we cannot properly filter these messages out of our error reporting systems because loginB randomly changes to some other user. We also can't reliably filter on trace ID because it could change.

My questions are:

  1. Is this a known bug or is it expected behavior? I have only found one article from someone posting evidence of this issue in their environment (http://www.360sqldba.com/2015/08/sql-error-log-shows-random-user-and.html). I haven't found anything official from Microsoft.
  2. Is there a way to reliably set the trace ID so that I can filter by trace ID rather than by login name?

I am seeing this happen in our SQL Server 2012 and SQL Server 2016 environments.

Best Answer

Found an answer here: http://www.360sqldba.com/2015/08/sql-error-log-shows-random-user-and.html

Conclusion: SQL error log message about which user and SPID stopped a timed trace is misleading. It appears that SQL uses a random active session to stop timed trace. It may be something that MS SQL team overlooked.

Related Question