Restrict the trace for one DB

Apr 27, 2011 at 11:26 PM

Hi. How can I restrict the trace to record the activity of one or two databases (not all), using the templates that are downloaded from the SQL Nexus site?

 

Thanks.

Coordinator
Apr 28, 2011 at 2:02 AM

You would need to add a parameters node in the .xml configuration file. The below filters activity on database name and would capture activity for only two databases: master and tempdb.

                  <Parameters>
                  <Parameter name=",@F0Col" value="35" />
                  <Parameter name="@F0Op" value="0" />
                  <Parameter name="@F0Filt" value="'tempdb'" />
                  <Parameter name="@F0LogOp" value="0" />
                  <Parameter name="@F1Col" value="35" />
                  <Parameter name="@F1Op" value="0" />
                  <Parameter name="@F1Filt" value="'master'" />
                  <Parameter name="@F1LogOp" value="0" />
                  </Parameters>
              </ProfilerCollector>

The @F0Col value is the Data Column name, in the above case Database Name whose value is 35. The column number values are available here: http://technet.microsoft.com/en-gb/library/ms190762(v=SQL.105).aspx

@F0LogOp is the logical operator and takes two values: 0 (AND) | 1 (OR)

@F0Filt is the value that your provide for the column filter. In this case tempdb and master.

@F0Op is the comparison operation. 0 = Equal To in the example above. The list of filter operations are available here: http://msdn.microsoft.com/en-us/library/ms174404.aspx

Apr 28, 2011 at 5:16 PM
Edited Apr 29, 2011 at 3:06 PM

Applying these changes, does not generate any files TRC.

The configuration file is, for example, SQLDiagPerfStats_Detailed_Trace2008.xml, right?

If I want to filter by DB_ID (neither master or msdb), I should write it as follows? 

                <Parameters>
                  <Parameter name="@F0Col" value="3" />
                  <Parameter name="@F0Op" value="0" />
                  <Parameter name="@F0Filt" value="21" />
                  <Parameter name="@F0LogOp" value="0" />
                  <Parameter name="@F1Col" value="3" />
                  <Parameter name="@F1Op" value="0" />
                  <Parameter name="@F1Filt" value="18" />
                  <Parameter name="@F1LogOp" value="0" />
                </Parameters>

 

 

Where in the file add this node? In this position?

                  
Name="User <EventType Configurable" enabled="false">
                    
Configurable name="User <Event 0" enabled="false" id="82" />
                    
Configurable name="User <Event 1" enabled="false" id="83" />
                    
Configurable name="User <Event enabled="false" id="84" 2" />
                    
Configurable name="User <Event 3" enabled="false" id="85" />
                    
Configurable name="User <Event 4" enabled="false" id="86" />
                    
Configurable name="User <Event 5" enabled="false" id="87" />
                    
Configurable name="User <Event 6" enabled="false" id="88" />
                    
Configurable name="User <Event 7" enabled="false" id="89" />
                    
Configurable name="User <Event 8" enabled="false" id="90" />
                    
Configurable name="User <Event 9" enabled="false" id="91" />
                  
</ EventType> 
                
</ Events> 
                <Parameters>
                  <Parameter name="@F0Col" value="3" />
                  <Parameter name="@F0Op" value="0" />
                  <Parameter name="@F0Filt" value="21" />
                  <Parameter name="@F0LogOp" value="0" />
                  <Parameter name="@F1Col" value="3" />
                  <Parameter name="@F1Op" value="0" />
                  <Parameter name="@F1Filt" value="18" />
                  <Parameter name="@F1LogOp" value="0" />
                </Parameters>
              </ ProfilerCollector>
              
Enabled="false" <ASProfilerCollector maxfilesize="350">
                
<Events>
                  
Name="Group1"> <EventType
                    
Name="AuditLogin" <Event enabled="true" id="0" />
                    
Name="AuditLogout" <Event enabled="true" id="0" />

Coordinator
May 5, 2011 at 3:40 PM

The position of the parameters node as shown by you is correct. However you need to change "@F0LogOp" value="0" to ="@F0LogOp" value="1" as the filter should read DBID = 21 OR DBID = 18. As per your filter above it reads DBID = 21 and DBID =18.

The query below will give you the trace filter information:

select * from fn_trace_getfilterinfo(<trace id>

) 

 

HTH

May 6, 2011 at 3:43 PM
Edited May 12, 2011 at 2:51 PM

Dear Amitban, thanks for responding again.

 Still not working. Or placing "@ F0LogOp" = 0 or placing = 1, no TRC file generated.

 I think the problem is the location of node. For example, if I put it here (as they advise me on http://social.msdn.microsoft.com/Forums/en-US/sqltools/thread/ac0322f4-7640-4c86-8f69-c98e4e3fed8e/):

<PerfmonObject name="\PhysicalDisk(*)" enabled="true">
  <PerfmonCounter name="\Avg. Disk Queue Length" enabled="true" />
  </PerfmonObject>
  </PerfmonCounters>
  </PerfmonCollector>
  </MachineCollectors>
<Instances>
<Instance name="*" windowsauth="true" ssver="10.50" user="">
      <Parameters>
                  <Parameter name=",@F0Col" value="35" />
                  <Parameter name="@F0Op" value="0" />
                  <Parameter name="@F0Filt" value="'people'" />
                  <Parameter name="@F0LogOp" value="0" />
        </Parameters>
<Collectors>
  <SqldiagCollector enabled="true" startup="false" shutdown="true" />
  <BlockingCollector enabled="false" pollinginterval="5" maxfilesize="350" />
<ProfilerCollector enabled="true" template="_GeneralPerformance90.xml" pollinginterval="5" maxfilesize="350">
<Events>
<EventType name="Database" enabled="true">
  <Event name="Data File Auto Grow" enabled="true" id="92" />

 TRC files are generated, but does not apply any filter.

 If the problem was the logical AND or OR, should generate an empty TRC. But I tried various combinations (AND, OR, <Parameter name="@F0Filt" value="people" />, <Parameter name="@F0Filt" value="'people'" />, etc), without being able to generate a TRC.

Best regards,

peregrino