CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

PRB: RML Utilities - ReadTrace and how to workaround MARS

PRB: RML Utilities - ReadTrace and how to workaround MARS

  • Comments 1

Summary

We've lately gotten several questions from users regarding ReadTrace and how to workaround when your input trace files contain MARS (Multiple Active Result Sets).  ReadTrace currently does not support processing MARS and you will need to use one of the following two workarounds to continue processing.

If you try to process a trace file that contains MARS you may see one of the following messages output by ReadTrace:

ERROR: SPID was detected using Multiple Active Result Sets (MARS).  ReadTrace can only process this set of trace files with -T35 enabled

ERROR: Session %1 was detected using Multiple Active Result Sets (MARS) with RequestId = %2. ReadTrace can only pseudo-process this set of trace files when command line parameter -T35 is specified

Workaround 1

The first workaround is to enable -T35 and then process again.  The -T35 trace flag applies a formula that will allow processing to continue for up to 10 request ids.  Typically enabling this trace flag is sufficient and you will be able to complete processing.  If your input has more than 10 requests ids then you will need to proceed with workaround 2.

Workaround 2

You enable -T35 and processing still fails with the following error message:

ERROR: SPID was detected using Multiple Active Result Sets (MARS) but batch id of "%1" is greater than allowed stagger range of 10 batch ids.

To continue processing use these steps.

1.  Import your trace files into a table.  See SQL Server Books Online fn_trace_gettable for instructions on how to do this.  For example:

select * into #tblTrace from fn_trace_gettable('C:\MyTrace.trc', 0)

2.  Update the table with the following to stagger the SPID and zero out the RequestID for all non zero RequestID:

update #tblTrace

     set SPID =

          case

               when RequestID = 0 then SPID

               else 32768 + (SPID * 20) + (RequestID % 20)

          end,

          RequestID = 0

3.  Save the updated trace table out to a file.  To do this, from the Profiler menu choose File -> Open -> Trace Table and pick your trace table.  Wait for the trace data to fully load.  This may take some time if your trace table is large.  Then after it loads go to File -> Save As and choose Trace File.

4.  Process the new trace file with ReadTrace and also use -T28 to ignore any column warnings.

More Information

It is also possible that you may have a situation where RequestIDs are greater than zero for connections that are HTTP connections and not MARS.  If you are not using MARS and you see HTTP in the TextData for the corresponding ExistingConnection or Audit Login event, you can just update the RequestID to zero. 

Sarah Henwood | Microsoft SQL Server Escalation Services

Leave a Comment
  • Please add 8 and 6 and type the answer here:
  • Post