Profiler - Can I trace a specific table?

Question

Post reply

Profiler - Can I trace a specific table?

Marcia Q

SSCrazy

Points: 2648
More actions
October 10, 2007 at 1:16 pm

#118956

I've run Profiler traces on a specific database, but I'm wondering if I can narrow it down even further to one particular table within a database.
I looked at sp_trace_setevent in bol and a couple of websites, but I didn't seem to see how to do it.
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Sergiy SSC Guru Points: 110208 More actions · Answer 1

There is "ObjectId" in the list of Filter options.

_____________
Code for TallyGenerator

Marcia Q SSCrazy Points: 2648 More actions · Answer 2

I'm just not following this. I thought I set up my filter correctly, but yet I'm getting queries from the entire server.

I know the ObjectID for my table is 1061734985. Can anyone see where I'm setting this up incorrectly?

--select id from mydb.dbo.sysobjects where name = 'RVTable'

declare @rc int

declare @traceid int

declare @maxfilesize bigint

set @maxfilesize = 250

exec @rc=sp_trace_create @traceid=@traceid output,

@options=0,

@tracefile=N'e:\trace\RVTableTrace_200710111700',

@maxfilesize=@maxfilesize,

@stoptime=NULL

if @rc > 0 print 'sp_trace_code failed with error code ' +

rtrim(cast(@rc as char))

else print 'traceid for the trace is ' + rtrim(cast(@traceid as

char))

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=1,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=10,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=6,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=11,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=12,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=13,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=18,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=16,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=17,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=9,@on=@on

exec sp_trace_setevent @TraceID,@eventid=10,@columnid=14,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=1,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=10,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=6,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=11,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=12,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=13,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=18,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=16,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=17,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=9,@on=@on

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=14,@on=@on

declare @intfilter int

set @intfilter = 1061734985

exec sp_trace_setfilter @TraceID, 22, 0, 0, @intfilter

exec sp_trace_setstatus @TraceID, 1

Thanks.

Sergiy SSC Guru Points: 110208 More actions · Answer 3

ObjectID is unique within database.

Same ObjectID could exist in every database.

You should keep DatabaseID in the filter.

_____________
Code for TallyGenerator

ChiragNS One Orange Chip Points: 26137 More actions · Answer 4

Hi

If your table name is unique in a server you can use objectname filter.

"Keep Trying"

Marcia Q SSCrazy Points: 2648 More actions · Answer 5

I still am getting results from all tables, but I found out something more...

I added the following events so that I could see the ObjectID and ObjectName columns in my results:

--ObjectID

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=22,@on=@on

--ObjectName

exec sp_trace_setevent @TraceID,@eventid=12,@columnid=34,@on=@on

When I look at the trace results, these two columns contain nulls. So that would make sense why the following filter doesn't work:

exec sp_trace_setfilter @TraceID, 34, 0, 0, N'RVTable'

Why would these columns be null? Is there some kind of setting I need to change or command I need to use so that the values of ObjectID and ObjectName are returned instead of a null?

Sergiy SSC Guru Points: 110208 More actions · Answer 6

I did not dig into it but I noticed that DatabaseName and ObjectName don't work for some reason. Probably they don't work in some cases, I don't know, was never interested.

I just always filter events by DatabaseID and ObjectId, and I never had problems with it.

_____________
Code for TallyGenerator

Marcia Q SSCrazy Points: 2648 More actions · Answer 7

But my ObjectID is null also. Is there a command I need to run so that ObjectID isn't null?

noeld SSC Guru Points: 96590 More actions · Answer 8

OBjectID is available for SP:Statement *NOT* For SQL:Batchcompleted *NEITHER* RPC:Completed . So if your queries are mostly stored procedures you are lucky but if like most cases you have batches you can't use ObjectID to filter 🙁

Cheers,

* Noel