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.

SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeature-800x182

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
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures1
  5. At this point, the New Session Wizard screen should have launched. Once the New Session Wizard has launched, click Next.
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures2
  6. Type “Deprecated and Discontinued” in the text box after session name and click Next.
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures3
  7. Choose the radial button  Do not use a template then click Next
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures4
  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.
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures5
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures6
  9. In the Capture Global Events section check the following:
  • client_app_name
  • client_connection_id
  • database_name
  • nt_username
  • sql_text
  • username
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures7
  1. Then click Next
  2. On the Set Session Event Filters screen click Next. We are not filtering this Event Session.
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures8
  3. 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.
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures9
  4. Click Next.
  5. On the next screen, click Finish on the Summary
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures10
  6. On the next screen click Close after the Event Session is successfully created.
    SQL SERVER - Identifying Deprecated SQL Server Features with Extended Events deprecatedfeatures11

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)

SQL Deprecated Feature, SQL Extended Events, SQL Scripts, SQL Server, SQL Server 2017
Previous Post
SQL SERVER – Identify Version of SQL Server from Backup File
Next Post
SQL SERVER – Steps to Migrate Cluster Storage from Old Disks to Newly Attached Disks

Related Posts

1 Comment. Leave new

  • Thanks for this pinal, I have always used MDMA for that in the past, but its always nice to try a different way

    Reply

Leave a Reply