How to Find SQL Server Deprecated Features Used by the Application? – Interview Question of the Week #165

There are many applications which are developed using an older version of SQL Server. As there are new releases happening, there are features, command, and syntax which are marked as deprecated features and might get removed in future version of SQL Server. It is always challenging for the developer to remember what is changed in the new release of SQL Server and what changes they needed to make sure that their application works with the latest version of SQL Server.

To make developer life easier, SQL Server has provided few features to get such information. If you are familiar with SQL Server Profiler, then you can refer event class shown in the following image.

The documentation on MSDN is available here.

Another method to capture this information is to create extended event session and add following two events:

1. deprecation_final_support
2. deprecation_announcement

Here is the syntax to create extended event session.

CREATE EVENT SESSION [DeprecationNotice] ON SERVER 
ADD EVENT sqlserver.deprecation_announcement(
            ACTION(package0.collect_system_time,sqlserver.database_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username)),
ADD EVENT sqlserver.deprecation_final_support(
            ACTION(package0.collect_system_time,sqlserver.database_name,sqlserver.sql_text,sqlserver.transaction_id,sqlserver.username))
ADD TARGET package0.event_file(SET filename=N'C:\Temp\DeprecationNotice.xel')
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=ON, STARTUP_STATE=OFF)
GO

We can use the following command to start the event session.

ALTER EVENT SESSION [DeprecationNotice] ON SERVER STATE = start;
GO

Once its running, a developer can let the users use the application and we can see analyze the XEL file to find various deprecated commands used. For testing purpose, I executed following commands in SSMS.

CREATE DATABASE OneDB
GO
SP_RENAMEDB 'OneDB','OneDB_Rename'
GO
SP_DBCMPTLEVEL 'master',100
GO

If you check documentation, you would find that sp_renamedb and sp_dbcmptlevel are the old command and marked for removal. We can see the same information in the extended event also. You can find the XEL files in the folder – C:\Temp\ in our case.

Have you used these events to make sure your application is not using any deprecated features? What are the other techniques you have used?

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