Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips


























































   Got a SQL tip?
            We want to know!

Identify deprecated SQL Server code with a server side trace

MSSQLTips author Aaron Bertrand By:   |   Read Comments   |   Related Tips: More > 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


Last Update: 3/24/2011


About the author
MSSQLTips author Aaron Bertrand
Aaron Bertrand is a Senior Consultant at SQL Sentry, Inc., and has been contributing to the community for over a decade, first earning the Microsoft MVP award in 1997.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.