March 11, 2009 at 1:06 pm
I recently had to reformat my system and one of the things I must have lost because I am now unable to find it is a really nifty scritp I picked up on this web site which returns the contents of 5 Deafult Trace files that SQL Server maintains. I thought I had added the item to my Briefcase but either I forgot to or the item was on another website but listed in an issue of the SQLServerCentral Daily newsletter.
The code pulled in the details of the 5 default Trace files (usually located in Microsoft SQL Server\MSSQL.1\MSSQL\LOG). I found a good piece by Adam Haines title 'Default Trace - A Beginners Guide' but it covers obtaining the trace data from 1 trc file.
Maybe there is no semi-automated way to pull the data form all 5 of the trace files but I swear I remember using this and doing so within the past 2 months. I did a search on the Function name and the first page of results did not return what I was looking for.
Does this ring a bell with anyone?
Thanks
Kindest Regards,
Just say No to Facebook!March 11, 2009 at 1:18 pm
Do you mean how to get in one select statement the content of all the trace files? If this is what you mean then just use the default value as the second parameter:
select * from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',default)
If you meant something else, pleas explain.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 11, 2009 at 1:25 pm
Adi Cohn (3/11/2009)
Do you mean how to get in one select statement the content of all the trace files? If this is what you mean then just use the default value as the second parameter:
select * from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',default)
If you meant something else, pleas explain.
Adi
I think you are correctly stating what I am asking but to make certain:
If I have 5 trace files in my \LOG directory (as listed in the original post) and the names of these 5 files are:
log_39.trc
log_40.trc
log_41.trc
log_42.trc
log_43.trc
I can run the below query and it will return the contents of all 5 of these trc files and not just the currently active one even though none of the log files are named log.trc?
select * from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',default)
Kindest Regards,
Just say No to Facebook!March 11, 2009 at 1:37 pm
If the files are called logXX.trc where XX is a number (doesn’t have to be 2 digits, but has to be an integer number), then yes. If the name is different, then no. You have to use the files’ name without the incrementing number. By the way, you don’t have to take my word for it. You can read in BOL about fn_trace_gettable, and you can also test it.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 11, 2009 at 1:41 pm
I just tested and querying log.trc returns 122 rows on my computer, I have 5 files today starting with log_833.trc. Querying log_833.trc returns 122 rows too, querying log_834.trc returns 99 rows etc. So the query for log.trc without numbers just gets the first file.
I am thinking of using xp_cmdshell with dir /b to output the folder content into a temp table, then the query will generate sql strings for select ....from ::fn_trace_gettable....
for all rows from the temp table where the file names end in trc and then the query outputs the result of executesql of these 5 strings into another temp table. This way you will get the content of all 5 logs into one temp table
Regards,Yelena Varsha
March 11, 2009 at 1:43 pm
Adi Cohn (3/11/2009)
If the files are called logXX.trc where XX is a number (doesn’t have to be 2 digits, but has to be an integer number), then yes. If the name is different, then no. You have to use the files’ name without the incrementing number. By the way, you don’t have to take my word for it. You can read in BOL about fn_trace_gettable, and you can also test it.Adi
I believe you.
I also already tested this real wuick with the desktop install of SQL 2008 I have on my Vista workstation.
Kindest Regards,
Just say No to Facebook!March 11, 2009 at 1:44 pm
i think it was a script i wrote and contributed:
Default Trace Load of All 5 Files
something i put together because i got annoyed to have to find the file name of each file;
Lowell
March 11, 2009 at 1:44 pm
Yelena Varshal (3/11/2009)
I juts tested and querying log.trc returns 122 rows on my computer, I have 5 files today starting with log_833.trc. Querying log_833.trc returns 122 rows too, querying log_834.trc returns 99 rows etc. So the query for log.trc without numbers just gets the first file.I am thinking of using xp_cmdshell with dir /b to output the folder content into a temp table, then the query will generate sql strings for select ....from ::fn_trace_gettable....
for all rows from the temp table where the file names end in trc and then the query outputs the result of executesql of these 5 strings into another temp table. This way you will get the content of all 5 logs into one temp table
Perhaps there is a difference between how SQL Server 2005 & 2008 do this because I juste tested it on my local workstation only install of SQL 2008 and it correctly read the contents of all 5 trc files.
Kindest Regards,
Just say No to Facebook!March 11, 2009 at 1:52 pm
BOL says:
"If number_files is specified as 'default', fn_trace_gettable reads all rollover files until it reaches the end of the trace. fn_trace_gettable returns a table with all the columns valid for the specified trace. "
I tried both and I am getting only the first table with both queries, in the first I specify 5 rollover files and in the second I specify Default:
select * from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',5)
select * from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',default)
Regards,Yelena Varsha
March 11, 2009 at 3:59 pm
Yelena Varshal (3/11/2009)
BOL says:"If number_files is specified as 'default', fn_trace_gettable reads all rollover files until it reaches the end of the trace. fn_trace_gettable returns a table with all the columns valid for the specified trace. "
I tried both and I am getting only the first table with both queries, in the first I specify 5 rollover files and in the second I specify Default:
select * from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',5)
select * from ::fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc',default)
I don't know what to tell you about that.
Anyone else monitoring this thread willing to test this and let us know your results?
Kindest Regards,
Just say No to Facebook!March 17, 2009 at 3:15 pm
Try this from Lowell:
--##################################################################################################
--Purpose: Load All The existing .trc files on a given server to a tracetable so they can be queried.
--Created By Lowell SQLServerCentral.com
--##################################################################################################
-- declare variables
DECLARE
@rowid INT,
@dir VARCHAR(1000),
@path VARCHAR(1000),
@sqlcmd VARCHAR(1000),
@filename VARCHAR(1000),
@TraceFileName NVARCHAR(256)
DECLARE @trn TABLE (rowid INT IDENTITY(1,1),FILE_NAME VARCHAR(1000))
CREATE TABLE #MyTraceTable (
TraceFile VARCHAR(128),
EventName VARCHAR(128),
StartTime DATETIME,
DatabaseID INT,
DatabaseName VARCHAR(128),
ObjectID INT,
TraceObjectName VARCHAR(128),
SysobjectsName VARCHAR(128),
type_desc VARCHAR(128),
ObjectType VARCHAR(128),
ObjectAbbrv VARCHAR(128),
LoginName VARCHAR(128),
HostName VARCHAR(128),
ApplicationName VARCHAR(128) )
-- set variables
-- might return a long filename like c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOGSELECT @path = REVERSE(SUBSTRING(REVERSE(path),CHARINDEX('\',REVERSE(path)),256)) FROM sys.traces WHERE is_default = 1
--select @path
SET @dir = ' dir "' + @path + '*.trc" /TW /OD /B' -- + @dir
-- this returns a collection of trace files...5 in my case
INSERT INTO @trn
EXEC xp_cmdshell @dir -- this statement will dump all your .trc files into a memory table
-- select * from @trn where file_name is not null
-- do stuff
SELECT @rowid = MIN(rowid) FROM @trn
WHILE @rowid IS NOT NULL
BEGIN
-- do stuff
SELECT @TraceFileName = @path + x.FILE_NAME FROM @trn x WHERE rowid = @rowid
INSERT INTO #MyTraceTable
SELECT
@TraceFileName,
ev.name,
tr.StartTime,
tr.DatabaseID,
tr.DatabaseName,
tr.ObjectID,
tr.ObjectName AS 'Trace ObjectName',
o.name AS 'Sysobjects Name',
o.type_desc,
tr.ObjectType,
sv.subclass_name AS 'ObjectAbbrv',
tr.LoginName,
tr.HostName,
tr.ApplicationName
FROM fn_trace_gettable(@TraceFileName, DEFAULT) tr
INNER JOIN sys.trace_events ev
ON tr.eventclass = ev.trace_event_id
INNER JOIN sys.trace_subclass_values sv
ON tr.eventclass = sv.trace_event_id
AND tr.ObjectType = sv.subclass_value
--and sv.trace_column_id = 28
LEFT JOIN sys.objects o
ON tr.ObjectID = o.OBJECT_ID
SELECT @rowid = MIN(rowid) FROM @trn WHERE FILE_NAME IS NOT NULL AND rowID > 0 AND rowid > @rowid
END
SELECT * FROM #MyTraceTable ORDER BY starttime
--drop table #MyTraceTable
Thanks to him....
MJ
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply