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.

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

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.

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

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.

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

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)

, , ,
Previous Post
What is Alternative to CASE Statement in SQL Server? – IIF Function – Interview Question of the Week #164
Next Post
SQL SERVER – How to Download SQL Server Native Client?

Related Posts

1 Comment. Leave new

  • Alexandros Pappas
    March 23, 2018 2:26 pm

    I want to upgrade an SQL Server 2008 R2 to SQL Server 2016. I would like to find the queries running in production (on SQL Server 2008 R2) that use discontinued features of SQL Server 2016. Is there a way to find these discontinued features?

    Reply

Leave a Reply

Menu