Replay trace errors on stored procedures | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Replay trace errors on stored procedures

Hi all, Captured a trace from our production machine and I am attempting to replay it on a production copy of our database. Everything works fine except any stored procedures that are longer than 48 characters are being truncated when re-run. This leads to the ‘Cannot Find Stored procedure’ error. Does anyone know how to get procs that are longer than 48 char to run in a trace replay. Thanks…
What version of SQL and SP do you have? Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
You can import that trace to a table and find out the description column for the length. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
I’m running SQL 2000 SP3 accoss the environment. I’m capturing the trace to a table and then replaying the data that is contained in the table. If I select from the table the stored procedure name is correct in the textdata column of the trace table. It even shows the correct exec statement with the full proc name in the replay window, but the next line is the ‘Replay Error’ statement and it shows the Cannot find stored procedure ‘proc name’ but the proc name is truncated. I’m thinking when the replay reads from the texdata column it picks up the full statement but only runs a certain length of chars. Microsoft KB does not have anything that I can find on this. Thanks…
Try to create the new trace and rerun. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Tried creating a new trace and re ran it but I get the same result, below is was I get LINE 1
SQL:BatchStarting – exec up_s_tps_locks_TtlSysKeyId_by_LockSysKeyId_for_ConcurMgmt 0, 100000023, 0 LINE 2
Replay Error – [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure ‘up_s_tps_locks_TtlSysKeyId_by_LockSysKeyId_for_ConcurMgmt’. (State 42000) (Code 2812)
oops, line 2 is cut off like this
LINE 2
Replay Error – [Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure ‘up_s_tps_locks_TtlSysKeyId_by_LockSysKeyId_for_C
Check whether this object resides on the database? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Actually, I think I may have been mistaken about the truncating of the proc, it is just displaying it in the textdata section truncated but when you look at what was actually run it is running the entire proc. The probem seems to be that the replay is runninng it in the wrong database. The DatabaseID that is stored in the trace table is correct but when the replay is done it is running everything in the msdb. Crazy…
Well that explains the issue…. check the database id on both the machines and ensure the trace points the correct one. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Yep, the db id’s are correctly pointing to the correct database.
I GOT IT! I had to set quoted identifiers on. Go figure… Thanks for all your help, much appreciated. …
]]>