Trace not grabbing database info

Question

Post reply

Trace not grabbing database info

jeff.wenker

Grasshopper

Points: 13
More actions
August 2, 2012 at 7:37 am

#261977

I've been using the below script to create traces on SS 2005 and SS 2008 R2 servers.
For some reason, I don't see one database (2008 R2) with this script. The only thing that has changed is we are now using mirroring.
I couldn't find anything online that this would affect it.
Any ideas?
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @MaxFileSize BIGINT
DECLARE @EndTime DATETIME
DECLARE @OutputFileName NVARCHAR(256)
SET @MaxFileSize = 200
SET @OutputFileName = '\\rnddev1\dbshare\RNDDB2' +
CONVERT(VARCHAR(20), GETDATE(),112) +
REPLACE(CONVERT(VARCHAR(20), GETDATE(),108),':','')
--SET @EndTime = DATEADD(mi,30,getdate())
SET @EndTime = DATEADD(mi,30,getdate())
exec @rc = sp_trace_create @TraceID output, 0, @OutputFileName, @MaxFileSize, @EndTime
-- Set the events and columns
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 16, @on --
exec sp_trace_setevent @TraceID, 10, 1, @on --
exec sp_trace_setevent @TraceID, 10, 17, @on --
exec sp_trace_setevent @TraceID, 10, 10, @on -- AppName
exec sp_trace_setevent @TraceID, 10, 11, @on -- Login Name
exec sp_trace_setevent @TraceID, 10, 18, @on --
exec sp_trace_setevent @TraceID, 10, 35, @on -- DatabaseName
exec sp_trace_setevent @TraceID, 10, 12, @on --
exec sp_trace_setevent @TraceID, 10, 13, @on --
exec sp_trace_setevent @TraceID, 10, 14, @on --
exec sp_trace_setevent @TraceID, 12, 16, @on --
exec sp_trace_setevent @TraceID, 12, 1, @on --
exec sp_trace_setevent @TraceID, 12, 17, @on --
exec sp_trace_setevent @TraceID, 12, 35, @on -- AppName
exec sp_trace_setevent @TraceID, 12, 11, @on -- LogName
exec sp_trace_setevent @TraceID, 12, 14, @on --
exec sp_trace_setevent @TraceID, 12, 18, @on --
exec sp_trace_setevent @TraceID, 12, 18, @on -- DatabaseName
exec sp_trace_setevent @TraceID, 12, 12, @on --
exec sp_trace_setevent @TraceID, 12, 13, @on --
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1

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

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

Lowell SSC Guru Points: 323755 More actions · Answer 1

i scripted out your trace, and ran it on my dev server(changing the path to the trace file only)

i certainly see trace data, but i'm confused about what you say you are not seeing.

you have a mirrored database, but you don't see any queries hitting that database? is that what you are saying?

here's how i look at the trace:

select * from sys.traces

--i now KNOW the trace is #2 because i peeked at all traces and saw the filename.

declare @path varchar(255)

SELECT @path = path from sys.traces WHERE id = 2 --my desired trace#

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

here's my readback of your trace definition:

--#################################################################################################

--Scripting trace_id 2 from server DEV223

-- Trace Last Started/Restarted on Aug 2 2012 9:43:40:730AM

-- Scripted for Analysis on Aug 2 2012 9:44:02:987AM

--#################################################################################################

--declare variables for parameterizing the command

declare @traceidout int

declare @myoptions int

declare @mypath nvarchar(256)

declare @mymaxfilesize bigint

declare @mymaxRolloverFiles int

declare @mystoptime datetime

declare @on bit

set @on = 1 --for scripting purposes, I think its better Always setting a script to start the trace after creation.

set @mymaxfilesize = 200 --size in MB

set @mymaxRolloverFiles = 1 --number of files; ie if 5 files, start rewriting on rollover

set @mystoptime = '2012-08-02 10:13:40.727' -- null if never ends, else a specific date

set @myoptions = 0 -- TRACE_FILE_ROLLOVER = FALSE, SHUTDOWN_ON_ERROR = FALSE

set @mypath = 'c:\data\RNDDB220120802094340' -- the trace adds ".trc" to the pathname, so avoid "name.trc.trc" by removing it for scripting

--#################################################################################################

--create the trace

exec sp_trace_create @traceid = @traceidout output, @options = @myoptions, @tracefile = @mypath, @maxfilesize = @mymaxfilesize, @stoptime = @mystoptime, @filecount = @mymaxRolloverFiles

--#################################################################################################

--Begin Event definitions

--#################################################################################################

exec sp_trace_setevent @traceidout,10,2,@on --RPC:Completed,BinaryData

exec sp_trace_setevent @traceidout,10,10,@on --RPC:Completed,ApplicationName

exec sp_trace_setevent @traceidout,10,11,@on --RPC:Completed,LoginName

exec sp_trace_setevent @traceidout,10,12,@on --RPC:Completed,SPID

exec sp_trace_setevent @traceidout,10,13,@on --RPC:Completed,Duration

exec sp_trace_setevent @traceidout,10,14,@on --RPC:Completed,StartTime

exec sp_trace_setevent @traceidout,10,16,@on --RPC:Completed,Reads

exec sp_trace_setevent @traceidout,10,17,@on --RPC:Completed,Writes

exec sp_trace_setevent @traceidout,10,18,@on --RPC:Completed,CPU

exec sp_trace_setevent @traceidout,10,35,@on --RPC:Completed,DatabaseName

exec sp_trace_setevent @traceidout,12,1,@on --SQL:BatchCompleted,TextData

exec sp_trace_setevent @traceidout,12,11,@on --SQL:BatchCompleted,LoginName

exec sp_trace_setevent @traceidout,12,12,@on --SQL:BatchCompleted,SPID

exec sp_trace_setevent @traceidout,12,13,@on --SQL:BatchCompleted,Duration

exec sp_trace_setevent @traceidout,12,14,@on --SQL:BatchCompleted,StartTime

exec sp_trace_setevent @traceidout,12,16,@on --SQL:BatchCompleted,Reads

exec sp_trace_setevent @traceidout,12,17,@on --SQL:BatchCompleted,Writes

exec sp_trace_setevent @traceidout,12,18,@on --SQL:BatchCompleted,CPU

exec sp_trace_setevent @traceidout,12,35,@on --SQL:BatchCompleted,DatabaseName

--#################################################################################################

--End Event definitions

--#################################################################################################

--begin filter definitions

--#################################################################################################

-- WHERE 1 = 1

--#################################################################################################

---end filter definitions

--#################################################################################################

---final step

--turn on the trace

exec sp_trace_setstatus @traceidout, 1 ---start trace

--exec sp_trace_setstatus TRACEID, 0 ---stop trace, you must know the traceid to stop it

--exec sp_trace_setstatus TRACEID, 2 ---close trace you must know the traceid to delete it

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!

jeff.wenker Grasshopper Points: 13 More actions · Answer 2

Thanks for giving the script a try.

"i certainly see trace data, but i'm confused about what you say you are not seeing.

you have a mirrored database, but you don't see any queries hitting that database? is that what you are saying?"

Yes, that's what I'm saying.

I'm getting info for Master, MSDB and ReportServer. The reports themselves are hitting the 'Missing' database, though I know there are other connections hitting it as well.

jeff.wenker Grasshopper Points: 13 More actions · Answer 3

Turns out the trace was pointed to the wrong server.

Thanks for looking,

Jeff