Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Default Trace Load of All 5 Files Expand / Collapse
Author
Message
Posted Monday, February 9, 2009 1:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 13,615, Visits: 34,754
Comments posted to this topic are about the item Default Trace Load of All 5 Files

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #653167
Posted Wednesday, July 3, 2013 4:23 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 21, 2015 4:57 PM
Points: 18, Visits: 205
very nice post. i was trying to write something similar to yours but was stuck on how to get the archived trace file locations.

I have few suggestions. i don't think this part is correct

INNER JOIN sys.trace_subclass_values sv
ON tr.eventclass = sv.trace_event_id
-- i think we have to use tr.EventSubClass instead of tr.objectType
AND tr.ObjectType = sv.subclass_value
-- can not join like this, objects can be from any database and sys.objects is local to each database
LEFT JOIN sys.objects o
ON tr.ObjectID = o.OBJECT_ID

-- to overcome the last problem. I created a temp table that has dbname,objectname,objectid and populated this table with all objects from all databases. then joined on dbname and objectid to get objectname
Post #1470262
Posted Wednesday, May 20, 2015 1:42 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:08 PM
Points: 1,718, Visits: 2,364
Hi unaur,

Can you post your code?


Thanks.
Post #1687402
Posted Wednesday, May 20, 2015 1:52 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 13,615, Visits: 34,754
wow that six year old article i wrote is just yucky.

this is how i read any current, live trace right now. hope this helps.
i used to actually put this into views , one for each trace. i'm trying to lean more towards extended evnets now.

--SELECT * from sys.traces
declare @TraceIDToReview int
declare @path varchar(255)

SET @TraceIDToReview = 1 --this is the trace you want to review!
SELECT @path = path from sys.traces WHERE id = @TraceIDToReview
SELECT
TE.name As EventClassDescrip,
v.subclass_name As EventSubClassDescrip,
T.*
FROM ::fn_trace_gettable(@path, default) T
LEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
LEFT OUTER JOIN sys.trace_subclass_values V
ON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_value




Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1687405
Posted Wednesday, May 20, 2015 2:03 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, May 21, 2015 4:57 PM
Points: 18, Visits: 205
here is my script as stored procedure

CREATE PROCEDURE sp_mon_default_trace(@starttime datetime = null, @LoginName varchar(100) = null, @eventid int = null)
AS

SET NOCOUNT ON;

-- source http://www.sqlservercentral.com/scripts/SQL+Trace/65790/
-- details https://www.simple-talk.com/sql/database-administration/collecting-the-information-in-the-default-trace/?

DECLARE @tracefile varchar(550)
DECLARE @rowid INT
DECLARE @dir VARCHAR(1000)
DECLARE @path VARCHAR(1000)
DECLARE @sqlcmd VARCHAR(1000)
DECLARE @CMD VARCHAR(1024)

IF @starttime IS NULL
SET @starttime = convert(varchar(10), getdate(), 101)

CREATE TABLE #TableList (
DatabaseName SYSNAME,
TableName SYSNAME,
ObjectId INT)

CREATE TABLE #MyTraceTable
(
TextData VARCHAR(8000),
HostName VARCHAR(128),
LoginName VARCHAR(128),
ApplicationName VARCHAR(128),
DatabaseName VARCHAR(128),
ObjectName VARCHAR(256),
CategoryName VARCHAR(256),
EventName VARCHAR(256),
SubEventName VARCHAR(256),
TraceObjectName VARCHAR(128),
TraceObjectType VARCHAR(128),
ObjectID INT,
EventID VARCHAR(128),
SubEventID VARCHAR(128),
StartTime DATETIME,
)

DECLARE @tracefiles TABLE (rowid INT IDENTITY(1,1),FILE_NAME VARCHAR(1000))

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

INSERT INTO @tracefiles
EXEC xp_cmdshell @dir -- this statement will dump all your .trc files into a memory table

SET @CMD = 'USE [?]; SELECT DB_NAME()DATABASE_NAME, NAME, OBJECT_ID FROM SYS.OBJECTS'

INSERT INTO #TableList
EXEC SP_MSFOREACHDB @CMD;

SELECT @rowid = MIN(rowid) FROM @tracefiles;

WHILE @rowid IS NOT NULL
BEGIN

SELECT @tracefile = @path + x.FILE_NAME FROM @tracefiles x WHERE rowid = @rowid

Insert into #MyTraceTable
select T.TextData,
T.HostName,
T.LoginName,
(CASE WHEN T.ApplicationName like 'SQLAgent - TSQL JobStep (Job %'
THEN j.name +' : '+(SUBSTRING(T.ApplicationName, CHARINDEX(':', T.ApplicationName) + 1, LEN(T.ApplicationName)-(CHARINDEX(':', T.ApplicationName)+1)))
ELSE T.ApplicationName END) as ApplicationName ,
T.DatabaseName,
L.TableName as ObjectName,
c.name as CategoryName,
E.name as EventClassName,
S.subclass_name as EventSubClassName,
T.ObjectName as TraceObjectName,
T.ObjectType as TraceObjectType,
T.ObjectID,
T.EventClass,
T.EventSubClass,
T.StartTime
FROM ::fn_trace_gettable(@tracefile,default) T
INNER JOIN sys.trace_events E with (nolock)
ON T.EventClass = E.trace_event_id
INNER JOIN sys.trace_categories C with (nolock)
ON C.category_id = e.category_id
LEFT OUTER JOIN sys.trace_subclass_values S with (nolock)
ON S.trace_event_id = T.EventClass and S.subclass_value = T.EventSubClass
LEFT OUTER JOIN #TableList L
ON L.OBJECTID = T.ObjectID and L.DatabaseName = T.DatabaseName
LEFT OUTER JOIN msdb.dbo.sysjobs J with (nolock)
ON (substring(left(j.job_id,8),7,2) +
substring(left(j.job_id,8),5,2) +
substring(left(j.job_id,8),3,2) +
substring(left(j.job_id,8),1,2)) = substring(T.ApplicationName,32,8)
WHERE T.SPID <> @@SPID;

SELECT @rowid = MIN(rowid) FROM @tracefiles WHERE FILE_NAME IS NOT NULL AND rowid > 0 AND rowid > @rowid
END

SELECT distinct * from #MyTraceTable
where StartTime > @starttime
and LoginName = ISNULL(@LoginName,LoginName)
and EventID = ISNULL(@EventID, EventID)
order by StartTime desc;

drop table #MyTraceTable;
drop table #TableList;
Post #1687407
Posted Wednesday, May 20, 2015 2:12 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:08 PM
Points: 1,718, Visits: 2,364
Thnx Folks...

Thanks.
Post #1687411
Posted Wednesday, May 20, 2015 2:41 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 10:07 AM
Points: 18,938, Visits: 17,213
Wow - amazing how these old threads come back to life sometimes ;)



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1687430
Posted Wednesday, May 20, 2015 2:48 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 5:08 PM
Points: 1,718, Visits: 2,364
It's all about requirement. :)

Thanks.
Post #1687432
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse