Administration

Profiler Trace – an Oddity

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.

Advertisements

One thought on “Profiler Trace – an Oddity

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s