My idea is to trace a few days all statements (scripted the replay template) on a SQL Server 2008 r2 and replay those against a deployed version of the db on a newer version (SQL Server 2014).

Is a re-playable trace file suitable to figure out server version compatibility issues? Does this scenario make sense?

How can one realize problems? How are errors displayed when replaying queries that have issues?

1 Answer 1


Before a replay, I would:

  1. Run the Upgrade Advisor and Best Practices Analyzer

  2. Run a trace looking for deprecation events

  3. Look for some of these issues; the above methods are unlikely to find all of them.

Note that none of the above, nor replaying the trace, are guaranteed to find all compatibility issues (consider the case where a stored procedure will only have a problem with very specific parameters are used, and those don't happen to be used during your trace).

And when you replay the trace, you'll have to parse the output file(s) or table(s) yourself, and perhaps even perform diffs to see any cases where there are errors on the new version that didn't happen on the old version. The replay doesn't produce a nice "here's what worked differently from the original activity" report. You can do this by simply performing a join against .

Here is a quick example I whipped up on my own system - you need to add the Error column to your trace, and note that I am absolutely not advocating Profiler for this, it was just the easiest way to demonstrate. First, on 2014, create a database using 100 compatibility level:


Now, run these two batches:

USE Compat100;
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- not style 121
-- error:

USE tempdb;
DECLARE @x TABLE(n DATETIME2, x AS CONVERT(CHAR(10), n)); -- style 121
-- not an error:

A trace will yield these:

enter image description here

(Again, do not use Profiler for the actual work, this was just to quickly illustrate that a trace can capture error conditions, and that if you had these two batches run identically on two different servers, you should easily be able to join on TextData equality and Error inequality. And yes, I do realize that this example works the opposite way - the error happens on 2008 but works on 2014, but hopefully it still demonstrates the approach.)

Also see this tip.

  • TY, good tips. Question: "even perform diffs" - how? Besides duration there is no success/failure output available, is it?
    – Magier
    Commented Aug 24, 2015 at 16:35
  • Aaron, TY, you showed my how to trace an error using profiler (I have scripted trace configurations and do not use profiler to create trace files). But my scenario is a little different: I traced the actual working statements from a live db (2008 comp. 80!) and wanted to replay exactly this trace against another instance (2014) instead of trace it again, because on the new unused instance, there is no activity to trace. I already figured that when Re-Running, there are sometimes things shown in Profiler like "Replay Provider Error"... but this was just because of a Primary Key violation.
    – Magier
    Commented Aug 24, 2015 at 17:41
  • I just found that the Upgrade Advisor Tool does not support to check databases having compatibility 80.
    – Magier
    Commented Aug 24, 2015 at 17:45
  • Does the tracing of deprecated events consider the compatibility level setting of the traces database?
    – Magier
    Commented Aug 24, 2015 at 17:54
  • If you want to know if statements suddenly error on the newer version, you'll have to re-run your trace, sorry. You could assume all statements completed without error in the old server, but it could lead to red herrings. Commented Aug 24, 2015 at 18:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.