Default Trace Load of All 5 Files
-
Comments posted to this topic are about the item Default Trace Load of All 5 Files
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! -
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
-
Hi unaur,
Can you post your code?
Thanks.
-
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.tracesdeclare @TraceIDToReview intdeclare @path varchar(255)SET @TraceIDToReview = 1 --this is the trace you want to review!SELECT @path = path from sys.traces WHERE id = @TraceIDToReviewSELECTTE.name As EventClassDescrip,v.subclass_name As EventSubClassDescrip,T.*FROM ::fn_trace_gettable(@path, default) TLEFT OUTER JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_idLEFT OUTER JOIN sys.trace_subclass_values VON T.EventClass = V.trace_event_id AND T.EventSubClass = V.subclass_valueLowell
--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! -
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 @tracefilevarchar(550)
DECLARE @rowidINT
DECLARE @dirVARCHAR(1000)
DECLARE @pathVARCHAR(1000)
DECLARE @sqlcmdVARCHAR(1000)
DECLARE @CMD VARCHAR(1024)
IF @starttime IS NULL
SET @starttime = convert(varchar(10), getdate(), 101)
CREATE TABLE #TableList (
DatabaseName SYSNAME,
TableName SYSNAME,
ObjectIdINT)
CREATE TABLE #MyTraceTable
(
TextData VARCHAR(8000),
HostNameVARCHAR(128),
LoginName VARCHAR(128),
ApplicationName VARCHAR(128),
DatabaseName VARCHAR(128),
ObjectNameVARCHAR(256),
CategoryNameVARCHAR(256),
EventNameVARCHAR(256),
SubEventNameVARCHAR(256),
TraceObjectName VARCHAR(128),
TraceObjectType VARCHAR(128),
ObjectID INT,
EventIDVARCHAR(128),
SubEventIDVARCHAR(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;
-
Thnx Folks...
Thanks.
-
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[/url]
Learn Extended Events -
It's all about requirement.
Thanks.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply