Server side Trace

Question

Server side Trace

sunny.tjk

SSCertifiable

Points: 5041
More actions
February 29, 2012 at 8:47 am

#252362

I'd like to set a filter on resource database in my server side trace. Could someone please tell me what filter do I set to eliminate resource database?

Viewing 12 posts - 1 through 11 (of 11 total)

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

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 1

I think I found the solution but I'm not 100% sure. Could someone verify if the following filter can be applied to exclude resource db:

set @intfilter = 32767

exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

Lowell SSC Guru Points: 323518 More actions · Answer 2

i added your filter to a trace and used my tool to script it back out;

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

--begin filter definitions

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

-- WHERE 1 = 1

exec sp_trace_setfilter @traceidout,3,0,1,32767 -- AND DatabaseID <> 32767

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

---end filter definitions

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

so as long as the databaseID really is 32767, you are right, that will exclude that database.

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!

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 3

Thanks Lowell.

Please correct me if I'm wrong--Isn't 32767 the databaseid of resource database?

Lowell SSC Guru Points: 323518 More actions · Answer 4

sunny.tjk (2/29/2012)
Thanks Lowell.
Please correct me if I'm wrong--Isn't 32767 the databaseid of resource database?

I'm sorry, i'm completely uninformed on that and i have no idea .

in my 2008 dev version, when i query sys.databases, the resource database does not appear;

my typical traces i create are threefold;

1. an enhanced default trace capturing DDL changes,

2. login information for whowuzzit info.

3. DML changes for whodunnit info.

so I've never noticed an entry ever referencing a database id higher than say, 50, which is about how many user databases i have floating around.

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!

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 5

No problem Lowell.

One last question:

Which one is correct?

exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter

OR

exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

I didn't understand the logical operator part, so I'm little confused.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

From books Online:

sp_trace_setfilter [ @traceid = ] trace_id

, [ @columnid = ] column_id

, [ @logical_operator = ] logical_operator

, [ @comparison_operator = ] comparison_operator

, [ @value = ] value

and

comparison_operator:

Value Comparison operator

0 = (Equal)

1 <> (Not Equal)

2 > (Greater Than)

3 < (Less Than)

4 >= (Greater Than Or Equal)

5 <= (Less Than Or Equal)

6 LIKE

7 NOT LIKE

32767 is the ID of the resource database, but there's little point in filtering it out. Because it can never be used (USE MSSystemResourceDB will give an error), the database id of an event won't relate to that DB.

Logical operator is just for when there are multiple filters and controls whether the filters are Filter1 AND Filter2 or whether they are Filter1 OR Filter2

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 7

Thanks Gail.

I ran the following query, please look at the results. On Feb 28th, there were 2865738 records for resource database. So, this is the reason I'd like to apply this filter.

select count(*) as [COUNT], convert(varchar, starttime, 110) as [DATE], DatabaseId

from dbatrace

group by convert(varchar, starttime, 110), DatabaseId

order by 2 desc

CountDateDatabaseID

282/29/201211

1117222/29/201232767

1682/29/20126

58602/29/20127

32/29/201212

8472/28/20126

3622/28/201211

28657382/28/201232767

643752/28/20127

52/21/201211

8382/21/201232767

8312/21/20127

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

What events are you tracing there?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 9

Here it is:

declare @on bit

set @on = 1

exec sp_trace_setevent @TraceID, 114, 1, @on

exec sp_trace_setevent @TraceID, 114, 3, @on

exec sp_trace_setevent @TraceID, 114, 8, @on

exec sp_trace_setevent @TraceID, 114, 10, @on

exec sp_trace_setevent @TraceID, 114, 11, @on

exec sp_trace_setevent @TraceID, 114, 12, @on

exec sp_trace_setevent @TraceID, 114, 14, @on

exec sp_trace_setevent @TraceID, 114, 15, @on

exec sp_trace_setevent @TraceID, 114, 21, @on

exec sp_trace_setevent @TraceID, 114, 26, @on

exec sp_trace_setevent @TraceID, 114, 28, @on

exec sp_trace_setevent @TraceID, 114, 34, @on

exec sp_trace_setevent @TraceID, 114, 40, @on

Lowell SSC Guru Points: 323518 More actions · Answer 10

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

--Begin Event definitions

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

exec sp_trace_setevent @traceidout,114,1,@on --Audit Schema Object Access Event,TextData

exec sp_trace_setevent @traceidout,114,3,@on --Audit Schema Object Access Event,DatabaseID

exec sp_trace_setevent @traceidout,114,8,@on --Audit Schema Object Access Event,HostName

exec sp_trace_setevent @traceidout,114,10,@on --Audit Schema Object Access Event,ApplicationName

exec sp_trace_setevent @traceidout,114,11,@on --Audit Schema Object Access Event,LoginName

exec sp_trace_setevent @traceidout,114,12,@on --Audit Schema Object Access Event,SPID

exec sp_trace_setevent @traceidout,114,40,@on --Audit Schema Object Access Event,DBUserName

exec sp_trace_setevent @traceidout,114,14,@on --Audit Schema Object Access Event,StartTime

exec sp_trace_setevent @traceidout,114,15,@on --Audit Schema Object Access Event,EndTime

exec sp_trace_setevent @traceidout,114,21,@on --Audit Schema Object Access Event,EventSubClass

exec sp_trace_setevent @traceidout,114,26,@on --Audit Schema Object Access Event,ServerName

exec sp_trace_setevent @traceidout,114,28,@on --Audit Schema Object Access Event,ObjectType

exec sp_trace_setevent @traceidout,114,34,@on --Audit Schema Object Access Event,ObjectName

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

--End Event definitions

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

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!

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 11

I used the following filter to the trace to exclude the resource database, but it didn't work. Is some wrong with it?

set @intfilter = 32767

exec sp_trace_setfilter @TraceID, 3, 0, 1, @intfilter