THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Learn Who Started that Trace with the Default Trace

This is not Extended Event related but it came from a question on Twitter about how to tell who and from what machine a server side trace was created, and there is no way to explain this in 140 characters so here’s a blog post.  This information is tracked in the Default Trace and can be found by querying for EventClass 175 which is the Audit Server Alter Trace Event trace_event_id from sys.trace_events.

select 
    trace_event_id, 
    name
from sys.trace_events
where name like '%trace%'

image

To query the default trace for this information we can do the following:

DECLARE @FileName VARCHAR(MAX)  

SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'  
FROM sys.traces   
WHERE is_default = 1;  

SELECT 
    LoginName, 
    HostName, 
    ApplicationName, 
    StartTime, 
    TextData
FROM sys.fn_trace_gettable( @FileName, DEFAULT ) AS gt  
WHERE EventClass = 175
Published Wednesday, December 29, 2010 3:00 PM by Jonathan Kehayias

Comments

 

Matt Cherwin said:

Well, now I feel silly. I've been manually tracking down the .trc file path every time I've needed to read from it. Pulling from sys.traces is better in every way.

So thanks! You've just made my life easier.

December 29, 2010 4:28 PM
 

Uri Dimant said:

Hi Jonathan

if I take a look at SELECT * FROM sys.traces I see default trace as

O:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log_766.trc

Starttime

2010-12-12 17:27:18.793

Now running your script I got

Microsoft SQL Server Management Studio - Query ,starttime =GETDATE()

HostName is my local machine not a server..

Am I missing something? Default trace is running since 12/12/2010 and was created on the server and not by me....

December 30, 2010 12:31 AM
 

Mehmet GÜZEL said:

Thank you for your answer.

December 30, 2010 4:37 PM
 

Jonathan Kehayias said:

Uri,

The default trace rolls over and has 5 trace files associated with it. That is why I don't use the file name directly from sys.traces, I use Log.trc so that it reads from the first file it finds in that name sequence to the last file.  You won't find information about when the default trace started in the default trace, you would only find information about traces that were started after the default trace was started.

The other thing is that trace_event_id 175 is raised everytime you query a trace with sys.fn_trace_gettable().  If you look at the TextData you will see the query and the call to fn_trace_gettable() so you can ignore those events.

Not sure if that addresses your question or not.

December 31, 2010 5:36 PM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement