Identify deprecated SQL Server code with a server side trace

By: Aaron Bertrand | Comments | Related: > Deprecated Features

Problem

SQL Server is evolving - new features are created, old features are removed. Whether or not the pace of upgrade in your environment is on the same 3-year cycle that Microsoft favors, you're going to come across situations where you are using features that are deprecated, and may stop working before the dust of your next upgrade settles. A lot of people are still using SQL Server 2000 and SQL Server 2005, even though we have seen SQL Server 2008 and 2008 R2 come and go, and are starting to brace ourselves for SQL Server 2012. If there is an upgrade in your future, you are more than likely going to have some work on your plate to in order to properly prepare.

Solution

For several versions, SQL Server has exposed SQL trace events that are triggered by the use of deprecated features. There are two events: Deprecation Announcement and Deprecation Final Support. From their respective Books Online topics, the definitions are as follows:

Deprecation Announcement Event Class
The Deprecation Announcement event class occurs when you use a feature that will be removed from a future version of SQL Server, but will not be removed from the next major release.
Deprecation Final Support Event Class
The Deprecation Final Support event class occurs when you use a feature that will be removed from the next major release of SQL Server.

So, the big things to take away from this are that the Deprecation Announcement events represent features that you'll want to start thinking about, and that the Deprecation Final Support events represent features that you need to start replacing in short order. Of course, the Deprecation Announcement events are more important if your next upgrade will involve skipping one or more versions (e.g. going from SQL Server 2005 to SQL Server 2012).

Some examples:

SQL Server 2008 / 2008 R2
      Deprecation Announcement
          - TEXT, NTEXT, and IMAGE data types
      Deprecation Final Support
          - DATABASEPROPERTY()
SQL Server 2012
      Deprecation Announcement
          - SET FMTONLY ON
      Deprecation Final Support
          - sys.soap_endpoints

Now that you know there are trace events to notify you of deprecated feature usage, your first instinct might be to fire up SQL Server Profiler and start a trace. However, even a filtered trace may not be a wise idea on a busy server; you are usually going to be much better off using a server-side trace, bypassing the UI altogether, and analyzing the trace data offline. I'm not suggesting that you shouldn't use Profiler at all; it can be an essential tool if you're using a correctly targeted trace. But I've learned that it is almost always better to use Profiler simply to script out your trace, not to actually run it or review events directly as they occur on the server. I will walk through that process in a future tip, but in this case, I just want to provide you with the code to create a server-side trace from Management Studio.

To create the trace, I like to use a minimalist approach, though it requires a bit of scaffolding. Profiler outputs a lot of verbose commands, particularly for sp_trace_setevent. If you know the event_id and column_id values you want to go after, it can be much easier to place those values in a couple of table variables, and build the commands using a loop. In this case I am interested in the following events and columns:

Events (sys.trace_events)
125	Deprecation Announcement
126	Deprecation Final Support
Columns (sys.trace_columns)
3	DatabaseID
8	HostName
10	ApplicationName
11	LoginName
12	SPID
14	StartTime
34	ObjectName

Assuming that c:\traces\ is a valid path, the following code can be used to set up a server-side trace that will track these events and columns (I've left out error handling, Profiler filtering and detailed output for brevity):

SET NOCOUNT ON;

DECLARE
   @trace_id INT,
   @sql      NVARCHAR(MAX),
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                -- this will create C:\traces\deprecated_n.trc:
   @path     NVARCHAR(256) = N'C:\traces\deprecated';

DECLARE @events TABLE(event_id SMALLINT);

INSERT  @events(event_id) SELECT trace_event_id
   FROM sys.trace_events WHERE trace_event_id IN (125, 126);

DECLARE @columns TABLE(column_id SMALLINT);

INSERT  @columns(column_id) SELECT trace_column_id
   FROM sys.trace_columns WHERE trace_column_id IN (3, 8, 10, 11, 12, 14, 34);

-- create the trace
EXEC sp_trace_create @traceid = @trace_id OUTPUT, @options = 2, @tracefile = @path;

-- build dynamic SQL will all the setevent calls
SELECT @sql = COALESCE(@sql, N'') + N'EXEC sp_trace_setevent @traceid = '
       + CONVERT(VARCHAR(5), @trace_id)   + ', @eventid = ' 
       + CONVERT(VARCHAR(5), e.event_id)  + ', @columnid = ' 
       + CONVERT(VARCHAR(5), c.column_id) + ', @on = 1;
   ' FROM @events AS e CROSS JOIN @columns AS c;

EXEC sp_executesql @sql;

-- turn the trace on
EXEC sp_trace_setstatus @traceid = @trace_id, @status = 1;

SELECT trace_id = @trace_id;

Note the trace_id somewhere, as it will be useful later. Of course, you can always look up trace details in sys.traces. If you want to check the net to see if you're catching any fish, you can run the following query:

DECLARE @path NVARCHAR(256);

SELECT @path = [path] 
   FROM sys.traces
   WHERE id = < trace_id from above >;

SELECT ObjectName, StartTime
   FROM sys.fn_trace_gettable(@path, DEFAULT)
   ORDER BY e.StartTime;

How long you let the trace run will be up to you. You may want to run it for an hour, a day, a week, or an entire business cycle. As long as you note the path to the trace file(s), you can always look at the data long after the the trace has been stopped and removed. You can shut down and delete the trace as follows:

EXEC sp_trace_setstatus 
    @traceid = < trace_id from above >,
    @status  = 0; -- stop trace

EXEC sp_trace_setstatus 
    @traceid = < trace_id from above >,
    @status  = 2; -- delete trace

Once you have a good idea of how many events you're dealing with and the timeframes they are most likely to occur, you can set up a wider trace that also collects the events SQL:BatchCompleted, SP:Completed and RPC:Completed. This will give you a much more direct line of sight into where the deprecation warnings are coming from, if the data in the deprecated-events-only trace. You can add these events to the trace simply by adding 10, 12 and 43 to the list of Event ID values in the @events table in the code above, and so that you can identify the code for any ad hoc SQL batches, you'll also want to add TextData (column_id = 1) to the @columns table. This will make for a much busier trace, so you should be careful to target this for a narrower window, and don't leave it running for a long time.

Note that, as Grant Fritchey points out in a recent blog post, it is not necessarily true that all deprecated features (such as certain showplan trace events) will appear in your trace. Hopefully what Grant points out is just a synchronization issue in SQL Server 2012 and not indicative of a broader trend.

In addition to using a trace, there are other ways to get this information. Starting with SQL Server 2008, detailed information is exposed by Extended Events. If you are still on SQL Server 2005, or if you haven't yet embraced Extended Events, there is a DMV, sys.dm_os_performance_counters, which shows an aggregated version of the information a targeted trace would show, and this same data is shown in Performance Monitor. Note that this only shows counts, does not differentiate between announcement and final support, and the data can be reset due to a SQL Server restart (in the case of the DMV) or a Windows restart (in the case of PerfMon).

Best Practices Analyzer / Upgrade Advisor

There are tools that Microsoft provides to help you identify some of these issues without having to roll up your sleeves too much. But please be aware that these tools will use static code analysis to find places where you're using these deprecated features or keywords, and this has limitations. For example, they won't find references to deprecated features or keywords that are constructed from dynamic SQL or passed in from an application.

That said, they are still very worthwhile tools to utilize, and I highly recommend running both of them - not just for discovering deprecated features, but also for pointing out fundamental issues that apply to multiple versions. You can find the download links for your target version in the first section of this blog post, "Useful, free resources for SQL Server."

Next Steps

Set up a server-side trace that monitors deprecation-related events 125 and 126.
Review the trace periodically to see whether you have problematic code or applications that will hamper future upgrade efforts. Load the trace files using a machine other than the production server, if possible.
Take - or at least plan - corrective action as necessary. Note that, in some cases, this may involve contacting a software vendor rather than the developers.
Review the following tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips