Today I’d like to document something I found using a server-side Profiler Trace. I noticed that the login name reported in the error log for the stop trace is erroneous. Let me explain…
We use a trace to monitor the performance of our application and have been for many years. We are currently using SQL 2014. We have a SQL Agent job that starts the trace using sp_create_trace and sp_trace_setstatus (yes, I know that we should be using extended events, and we will someday, but it’s not real high on the priority list). You can find examples of this all over the internet so I won’t bore you with those details. When a trace starts you will find an entry in the SQL Server Error Log like this:
SQL Trace ID 2 was started by login “My SQL Agent Account”.
Now here’s the odd part. When the trace is created we use the stop time parameter to automatically stop the trace each day. I noticed that the SQL Server Error Log contains this message:
SQL Trace stopped. Trace ID = ‘2’. Login Name = ‘My App Account’.
SQL Server is reporting that the SQL login that we use for our application is being used to stop the trace. Crazy, you say? Doing more digging I found some information that indicates that, since the stop trace function is automatic and NOT directly initiated by any database user, that SQL Server uses the SQL login from the last trace entry to report the stop trace. So don’t freak out (like I did) when you see that one of your “regular” SQL logins is stopping the trace; it really isn’t.