First published on MSDN on Jan 21, 2009
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
