Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Events          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Identifying Deprecated SQL Server Code with Profiler


By:   |   Read Comments (9)   |   Related Tips: More > Deprecated Features

Problem
In your recent tip (WARNING: SQL Server Deprecated Features) you have outlined a number of deprecated features.  The listing with the mapping to the new code set is beneficial.  Unfortunately I have an enterprise SQL Server environment and I need to be able to identify the deprecated features in more of an automated manner.  We do not have the time to check all of our code (stored procedures, functions, etc.) and I know we have T-SQL embedded in middle tier and front end applications.  How can I analyze this code in an efficient manner?

Solution
In any sized environment, identifying the deprecated SQL Server features could be a long and time consuming process.  Luckily, Microsoft was thinking ahead and has events in Profiler that can be captured to identify the code with deprecated features.  Let's take a look at how to capture the T-SQL code:

Launch SQL Server 2005 Profiler

Navigate to Start | All Programs | Microsoft SQL Server 2005 | Performance Tools | SQL Server Profiler.

 

Start a New Trace

Navigate to File | New Trace... | Authenticate to the SQL Server you want to monitor

 

General Tab - Configurations

Specify the name and configure the results to be saved to a table.

 

Events Selection Tab - Configurations

In order to review all events, enable the check box for 'Show all events' and 'Show all columns' on the bottom right of the screen.

Then navigate to the 'Deprecation' heading and enable the 'Deprecation Announcement' and 'Deprecation Final Support' events.

Once you have made these configurations or others as desired, then press the 'Run' button to begin the Profiler session.

 

Profiler Results - Deprecated Features

As an example, if you issue this command:

BACKUP TRAN <DatabaseName> WITH NO_LOG
GO

The results in Profiler will be:

 

Additional Analysis - Query the Results

SELECT *
FROM <TableName>
WHERE EventClass IN (125, 126);
GO

Next Steps

 



Last Update:






About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





More SQL Server Solutions




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, November 04, 2014 - 9:32:07 AM - Eric S Back To Top

I've run into the same issue as Randy M. It looks like the deprecation feature is not available for 2000 databases as when I target a 2008 R2 database, the option does appear using the same sql profiler version. Has any one found a way to look for deprecated SQL in a 2000 database?


Friday, November 08, 2013 - 11:54:05 AM - Jeremy Kadlec Back To Top

Alessio,

Great question.  First I would run Profiler for a representative amount of time to capture all of your critical processes to verify all you do not have any deprecated code.  I would also change the database compatibility mode in your Dev and Test environments well in advance of production.  Then test your application in your Dev and Test environments to ensure there are no issues.

HTH.

Thank you,
Jeremy Kadlec
Community Co-Leader

 


Thursday, November 07, 2013 - 6:34:47 AM - Alessio Back To Top

Hi Jeremy

I would like to ask you a suggestion.

I've a (large) db on SQLServer 2008 R2, in compatibility level 80 and i would like set it to compatibility level 100

Do you think that are sufficient run profiler to identify deprecated features before change the compatibility level?

Do you have other suggestions ?

Thank you in advance.


Saturday, October 12, 2013 - 8:37:58 PM - Jeremy Kadlec Back To Top

Randy,

Let me see if I can find a good test environment to test out that scenario.

Thank you,
Jeremy Kadlec
Community Co-Leader


Monday, September 23, 2013 - 4:37:38 PM - Randy M Back To Top

The server I'm trying to get the Deprecated Events from is a SQL 2000 box. I'm trying to run Profiler from a SQL 2005 server against the 2000 - build 2187 box. Unfortunately, the Deprecation Events don't appear as a choice whether you check "show all Events" or not. I was wondering if you had any idea's on how you would proceed. 


Sunday, September 22, 2013 - 3:49:23 PM - Jeremy Kadlec Back To Top

Randy,

I am not sure I understand your question 100%, but I am wondering if you did not select the check box for "Show all Events" as shown in the 4th image above.  See if that helps.

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, September 19, 2013 - 12:45:24 PM - Randy M Back To Top

Hay Jeremy, I realize this is an older article but, I'm trying this out on a SQL 2005 Std instance with Service Pack 4 and it doesn't have the Deprecation Events that you show in the picture. The Events aren't in this machines profiler. Any idea's why?


Thursday, September 16, 2010 - 11:50:52 AM - Admin Back To Top
Tony,

Good question.  In this circumstance, you want to run the code on the SQL Server 2008 instance.

Thank you,
The MSSQLTips Team


Thursday, September 16, 2010 - 9:54:26 AM - Tony Henley Back To Top
To be proactive, do you need to run profiler on the SQL Server version, to which you are migrating? (i.e you are on SQL 2008 and want to migrate to SQL 2008 R2, so you should run profiler on SQL Server 2008 R2?)


Learn more about SQL Server tools