Apr 022009

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

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.

  One Response to “Finding The Deprecated Statement”

  1. Very nice script. I hadn’t thought of using the SqlHandle from the trace file in that way. Lots of possibilities open up with that.

 Leave a Reply



You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>