This project is read-only.

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.

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" />

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