SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Default Trace Load of All 5 Files


Default Trace Load of All 5 Files

Author
Message
Lowell
Lowell
SSC Guru
SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)

Group: General Forum Members
Points: 137218 Visits: 41515
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!
unaur
unaur
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 207
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
SQL-DBA-01
SQL-DBA-01
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14780 Visits: 3719
Hi unaur,

Can you post your code?

Thanks.
Lowell
Lowell
SSC Guru
SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)SSC Guru (137K reputation)

Group: General Forum Members
Points: 137218 Visits: 41515
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
--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!
unaur
unaur
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 207
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;
SQL-DBA-01
SQL-DBA-01
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14780 Visits: 3719
Thnx Folks...

Thanks.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)SSC Guru (110K reputation)

Group: General Forum Members
Points: 110180 Visits: 18623
Wow - amazing how these old threads come back to life sometimes Wink



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SQL-DBA-01
SQL-DBA-01
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14780 Visits: 3719
It's all about requirement. Smile

Thanks.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search