SQL Server – Knowing Deprecated or Discontinued Features Using Extended Events

This blog is in continuation to what I wrote couple of weeks back – SQL Server – Knowing the Use of Deprecated or Discontinued Features. The concept of using a deprecated feature is not by intensions by developers but is an accident for many. When I wrote that blog post, lesser did I know people are going to ask me for more. One of my reader mentioned using SQL Server Profiler to identify the same. Though this is a valid answer, I wanted to keep Profiler away for this and introduce him to a new method of using Extended Events for the same requirements.

With every new release of SQL Server, the investments that are going to Extended Events is amazing. So I think to bring the same in a step-by-step for folks who want to use the same for identifying Deprecated features. Let us work through the basic steps in setting this up next:

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 ‘deprec’ 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 5 MB with Rollover files as 20.
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.

The same can be scripted out using the script button in the wizard before you hit the finish button. The script for the same steps would look like:

CREATE EVENT SESSION [Deprecated and Discontinued] ON SERVER
ADD EVENT sqlserver.deprecation_announcement(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_username, sqlserver.sql_text,
sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,
sqlserver.database_name,sqlserver.nt_username, sqlserver.sql_text,
sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'Deprecated and Discontinued',max_file_size=(5),max_rollover_files=(20))
WITH (STARTUP_STATE=OFF)
GO

If you want to use the same. Go ahead and start the session for Extended Events and start the collection process.

Extended Events are cool in a lot of ways. As part of my learning adventures, Extended Events are slowly getting into mainstream. Having said that, I have tons to learn from you. Do let me know how you use Extended Events in your environments and let me know via your comments.

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