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 The results in Profiler will be:
|
Additional Analysis – Query the Results |
| SELECT * FROM <TableName> WHERE EventClass IN (125, 126); GO |
Next Steps
- As you begin to analyze your environment for a migration to SQL Server 2005 or 2008, be sure to keep the deprecated features in mind.
- Be sure to understand the mapped T-SQL commands to be able to continue business operations.
- To streamline the identification of the deprecated features consider using SQL Server 2005 Profiler.
- Check out these related tips:
- WARNING: SQL Server Deprecated Features
- Capturing Graphical Query Plans with Profiler
- Capturing Deadlock Information in XML Format
- SQL Server Performance Statistics Using a Server Side Trace
- Correlating Performance Monitor and Trace Data
- Deprecation Final Support Event Class
- Deprecation Announcement Event Class
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.