Here is A Default Trace Example of all trace files

Question

Post reply

Here is A Default Trace Example of all trace files

Lowell

SSC Guru

Points: 323518
More actions
February 6, 2009 at 7:14 pm

#199780

I read someones example on how to load the default trace...it used a method to get the current default trace file...I thought fine, but if it looks for one file, there must be more than one.
Well I read that the default trace keeps the last 5 meg of info, and a max of 5 files...apparently you can't change the default trace to keep more than that(someone prove me wrong...please!)
so combining one example of finding all files in a folder, here's a simple script that loads all the trace files into a table so you can look at all the trace events.
add this to your bag-o-tricks.
[font="Courier New"]--##################################################################################################
--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\LOG\
SELECT @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
[/font]
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 1

Nice Lowell! I review the default trace a good bit and I'm sure this will be useful!

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

ravikanth. Hall of Fame Points: 3472 More actions · Answer 2

ravikanth.

Hall of Fame

Points: 3472

February 7, 2009 at 12:57 pm

#940051

Thanks Lowell....This will be useful for me.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

Awesome Lowell!!:cool:! Thanks a lot for sharing.... thats a good work....keep going.......:D

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Lowell (2/6/2009)
Well I read that the default trace keeps the last 5 meg of info, and a max of 5 files...apparently you can't change the default trace to keep more than that(someone prove me wrong...please!)

Mostly correct. They're 20MB in size, other than that, you're right.

In future, please consider contributing scripts to the script library here instead of posting them in the forums where they'll be forgotten. If you do, you'll get to see your name in the newsletter, and the script will be relativly eay to find by anyone searching through the library.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass