READ Data from default Trace

  • Comments posted to this topic are about the item READ Data from default Trace

    Thanks.

  • Did it. Tried it. Looks cool. Not sure how I'm going to make use of it yet but I'll find a use. Thanks.

  • Note quite sure what it does, but stored proc could be simplified as follows (yes?):

    Create PROC USP_ALTEROBJECTS @dbname varchar(100) = ''

    as

    BEGIN

    DECLARE @filename VARCHAR(255)

    SELECT @FileName = SUBSTRING(path, 0, LEN(path)-CHARINDEX('\', REVERSE(path))+1) + '\Log.trc'

    FROM sys.traces

    WHERE is_default = 1;

    PRINT @FileName

    IF @dbname='' set @dbname=db_name()

    SELECT gt.HostName,

    gt.ApplicationName,

    gt.NTUserName,

    gt.NTDomainName,

    gt.LoginName,

    gt.SPID,

    gt.EventClass,

    te.Name AS EventName,

    gt.EventSubClass,

    gt.TEXTData,

    gt.StartTime,

    gt.EndTime,

    gt.ObjectName,

    gt.DatabaseName,

    gt.FileName,

    gt.IsSystem

    FROM [fn_trace_gettable](@filename, DEFAULT) gt

    JOIN sys.trace_events te ON gt.EventClass = te.trace_event_id

    WHERE EventClass in (164) AND DatabaseID = db_id(@dbname)

    ORDER BY StartTime DESC;

    END

  • This does not work:

    IF @dbname <> NULL

    It needs to be

    IF @dbname IS NOT NULL

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • I think you only getting it from current active trace file, but there are more trace files, might have useful info.

    check this post on how to get all the trace files http://www.sqlservercentral.com/scripts/SQL+Trace/65790/

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

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