READ Data from default Trace

Question

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

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

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 1

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.

OverTheHill Grasshopper Points: 21 More actions · Answer 2

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

Michael L John One Orange Chip Points: 27055 More actions · Answer 3

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/

unaur SSC Veteran Points: 200 More actions · Answer 4

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