Geek Answers Handbook

How to reproduce SQL Profiler trace in another database?

I captured the trace (template: tsql_replay) on our production server for a specific database (filtered by database name). I would like to play it on our test server, but, of course, the database identifier on the test server is different.

So far I have tried loading the trace file into a table and modified databaseid with an update request (see below), but Profiler gives me an error when trying to play it (missing events, etc.). If I open the trace file directly, it allows me to play it. One way or another, either loading into the table or changing the database identifier causes something.

What is the correct procedure to capture a trace to play and modify the database on which it should be played?

select * into trace_table
from fn_trace_gettable('f:\trace\trace100222.trc', default)
go
update trace_table
set databaseid = 47
where databaseid = 16
+5
source share
3 answers

You need to create a trace and save it as Trace XML File for Replay , then open the XML and change the database identifier in the XML trace .

Remember that if you spend from sql 2008 to sql 2000, you cannot set the end date and hour, because there will be an ODBC DateTime error.

But other than that, it works fine.

+3
source

This is two years later, but I had the same problem. Here's how I solved it:

  • Launch SQL Server Profiler
  • Open the trace file
  • , .
  • (T1)
  • ( LoginName )

, , Profiler , fn_trace_gettable, . , , sp_cursor, :

exec sp_cursor 180150003,4,0,N'[dbo].[T1]',@EventClass=65528,@BinaryData=0xFFF...
+1

(.trc). XML. SQL 2005. , , . TSQL_Replay .

SPID " " . .

0

More articles:


All Articles