Default Trace Load of All 5 Files

  • Lowell

    SSC Guru

    Points: 323398

    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

    SSC Veteran

    Points: 200

    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_Hunt

    SSC-Dedicated

    Points: 33267

    Hi unaur,

    Can you post your code?

    Thanks.

  • Lowell

    SSC Guru

    Points: 323398

    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

    SSC Veteran

    Points: 200

    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_Hunt

    SSC-Dedicated

    Points: 33267

    Thnx Folks...

    Thanks.

  • SQLRNNR

    SSC Guru

    Points: 281210

    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

  • SQL_Hunt

    SSC-Dedicated

    Points: 33267

    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