Finding The Deprecated Statement

Following on from my previous post regarding using Profiler to find the use of deprecated features, I received an email asking if there was a way to determine what the offending statement was that caused the deprecation warning.

While you could trace every statement on a specific SPID to identify when the warnings are coming through, a much better method is to use the SQLHandle column in the Deprecation Announcement Profiler event.  This gives you a SQL Plan Handle that you can then use the SQL Server 2005 dynamic management  views/functions to look up the plan, including the original SQL text.

First, save the trace file to disk, and then run the following query:

SELECT Trace.TextData, SqlText.Text, *
FROM fn_trace_gettable('c:\work\deprecatedtrace.trc', 1) AS Trace
OUTER APPLY sys.dm_exec_sql_text(Trace.SqlHandle) AS SqlText
WHERE TextData IS NOT NULL

There is a drawback to this method, being that the plan must still be in the procedure cache.  If the server has restarted, or the plan has been removed or expired from the cache, you won’t be able to find the plan.