Identifying Deprecated SQL Server Code with Profiler

By:   |   Updated: 2007-11-09   |   Comments (9)   |   Related: > 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.

Profiler InitialScreen 1

 

Start a New Trace

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

Profiler Login 2

 

General Tab - Configurations

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

Profiler GeneralTab 3

 

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.

Profiler ShowAllEvents 4

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

Profiler DeprecationEvents 5

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:

Profiler DeprecationExample 6

 

Additional Analysis - Query the Results

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

 



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

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

View all my tips


Article Last Updated: 2007-11-09

Comments For This Article




Tuesday, November 4, 2014 - 9:32:07 AM - Eric S Back To Top (35179)

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 8, 2013 - 11:54:05 AM - Jeremy Kadlec Back To Top (27450)

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 7, 2013 - 6:34:47 AM - Alessio Back To Top (27436)

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 (27135)

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 (26901)

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 (26891)

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 (26866)

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 (10164)
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 (10163)
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?)














get free sql tips
agree to terms