SQL SERVER – Identifying Deprecated SQL Server Features with Extended Events

In this blog post with the help of Extended Events, we will learn how to identify all the deprecated SQL Server Features. This feature can be very much handy when we are changing the compatibility of SQL Server and we want to check what are all the deprecated SQL Server features we are still using in our active code.

Let us see a very simple tutorial:

1. Open the SQL Server Management Studio.
2. Connect to the server next.
3. Click the plus sign next to Management then click the plus sign next to Extended Events.
4. Click on Sessions then right click on New Session Wizard
   
   
5. At this point, the New Session Wizard screen should have launched. Once the New Session Wizard has launched, click Next.
   
6. Type “Deprecated and Discontinued” in the text box after session name and click Next.
   
7. Choose the radial button  Do not use a template then click Next
   
   
8. In the Event library text box type ‘deprecated’ and choose the deprecation announcement and deprecation final support events and then click the > arrow in the middle of the page to move them over as Selected events.
   
   
9. In the Capture Global Events section check the following:

• client_app_name
• client_connection_id
• database_name
• nt_username
• sql_text
• username
  
  

10. Then click Next
11. On the Set Session Event Filters screen click Next. We are not filtering this Event Session.
    
12. Check the top check box next to Save data to a file for later analysis Make the Max File Size 10 MB with Rollover files as 5.
    
13. Click Next.
14. On the next screen, click Finish on the Summary
    
    
15. On the next screen click Close after the Event Session is successfully created.
    

Now let us run any deprecated feature in SQL Server and see the output. As I am using SQL Server 2017, I know that SET ROWCOUNT feature is deprecated in SQL Server 2017. Let us run the same command in SQL Server.

Next, change your focus to live data on the screen and you will be able to see that extended event screen will catch the deprecated event in the windows and display all the necessary information.

I guess it is very easy to know what are the deprecated features you are using across the database. We use this feature all the time during Comprehensive Database Performance Health Check.

Reference: Pinal Dave (https://blog.sqlauthority.com)