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!

Identifying Deprecated SQL Server Code with Profiler

MSSQLTips author Jeremy Kadlec By:   |   Read Comments (8)   |   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: 11/9/2007


About the author
MSSQLTips author Jeremy Kadlec
Jeremy Kadlec is the CTO @ Edgewood Solutions, co-founder of MSSQLTips.com, Baltimore SSUG co-leader and SQL Server MVP since 2009.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, September 16, 2010 - 9:54:26 AM - Tony Henley Read The Tip
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?)


Thursday, September 16, 2010 - 11:50:52 AM - Admin Read The Tip
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 19, 2013 - 12:45:24 PM - Randy M Read The Tip

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?


Sunday, September 22, 2013 - 3:49:23 PM - Jeremy Kadlec Read The Tip

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


Monday, September 23, 2013 - 4:37:38 PM - Randy M Read The Tip

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. 


Saturday, October 12, 2013 - 8:37:58 PM - Jeremy Kadlec Read The Tip

Randy,

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

Thank you,
Jeremy Kadlec
Community Co-Leader


Thursday, November 07, 2013 - 6:34:47 AM - Alessio Read The Tip

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.


Friday, November 08, 2013 - 11:54:05 AM - Jeremy Kadlec Read The Tip

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

 



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.