Find Deprecated SQL Server Features with Extended Events

To find deprecated SQL Server features in your code, capture two Extended Events and read what they report. A deprecated feature still works today. It is the one that breaks on the day you upgrade, so you want the list before that day.

Why Check for Deprecated SQL Server Features Before You Upgrade

Deprecated SQL Server features are on their way out. SQL Server keeps running them, and the documentation tells you to stop using them in new work. The risk sits in old code. A procedure written years ago can use a data type or a hint that a later version drops. It can also read a system table that is gone.

Two events report this while the code runs. deprecation_announcement fires for features that will be removed in a future version. deprecation_final_support fires for features that won’t exist in the next major version. Treat the second list as urgent.

A Quick Look at the Counters

Before you build anything, ask the server what it already counted. SQL Server keeps one counter per deprecated feature, and the counts grow from the last restart.

SELECT TOP (8) RTRIM(instance_name) AS Feature, cntr_value AS UsesSinceStartup
FROM sys.dm_os_performance_counters
WHERE object_name LIKE N'%Deprecated Features%'
  AND counter_name = N'Usage'
  AND cntr_value > 0
ORDER BY cntr_value DESC;

The counters cover the whole server, and your list will differ. They tell you what is used, not where. The server holds 257 of these counters, and 35 had a count above zero when this ran. For the where, you need a session.

Capture the Details With an Event Session

The script creates a demo database and an event session. The session listens for both events. It records the database name, the client application and the text of the batch. A filter keeps it to the demo database, so noise from other databases stays out. The ring buffer target holds events in memory.

IF DB_ID(N'DeprecatedFeatureDemo') IS NULL CREATE DATABASE DeprecatedFeatureDemo;
GO
USE DeprecatedFeatureDemo;
GO
IF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'DeprecatedFeatureDemoSession')
    DROP EVENT SESSION DeprecatedFeatureDemoSession ON SERVER;
GO
CREATE EVENT SESSION DeprecatedFeatureDemoSession ON SERVER
ADD EVENT sqlserver.deprecation_announcement (
    ACTION (sqlserver.database_name, sqlserver.sql_text, sqlserver.client_app_name)
    WHERE sqlserver.database_name = N'DeprecatedFeatureDemo'),
ADD EVENT sqlserver.deprecation_final_support (
    ACTION (sqlserver.database_name, sqlserver.sql_text, sqlserver.client_app_name)
    WHERE sqlserver.database_name = N'DeprecatedFeatureDemo')
ADD TARGET package0.ring_buffer (SET max_memory = 4096);
GO
ALTER EVENT SESSION DeprecatedFeatureDemoSession ON SERVER STATE = START;

In SSMS 22 you can build the same session without code. Open Management, then Extended Events, then Sessions, and choose New Session Wizard. Pick the two events by searching for the word deprecat. For a long capture, use a file target instead. A ring buffer is lost when the session stops.

Run Some Deprecated Code

The next batches use four old habits. They are a text column, SET ROWCOUNT before an update, a table hint without WITH, and sp_dbcmptlevel. Each batch is separate, so the session records which one fired.

DROP TABLE IF EXISTS dbo.Trays;
GO
CREATE TABLE dbo.Trays (TrayID int NOT NULL PRIMARY KEY, Note text NULL);
GO
INSERT INTO dbo.Trays (TrayID) VALUES (1), (2), (3);
GO
SET ROWCOUNT 2;
UPDATE dbo.Trays SET TrayID = TrayID + 10;
SET ROWCOUNT 0;
GO
SELECT TrayID FROM dbo.Trays (NOLOCK);
GO
SELECT TrayID FROM dbo.Trays WITH (NOLOCK);
GO
EXEC sp_dbcmptlevel N'DeprecatedFeatureDemo';

Read What Was Captured

The ring buffer holds XML. This query turns each event into a row. Each row shows the event name, the feature and the start of the batch that caused it. The text is the whole batch, so a feature inside a long batch means you search that batch.

SELECT e.n.value('(@name)[1]', 'nvarchar(60)') AS EventName,
       e.n.value('(data[@name="feature"]/value)[1]', 'nvarchar(100)') AS Feature,
       LEFT(REPLACE(REPLACE(e.n.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)'), CHAR(13), N' '), CHAR(10), N' '), 50) AS StatementStart
FROM (SELECT CAST(t.target_data AS xml) AS x
      FROM sys.dm_xe_session_targets AS t
      JOIN sys.dm_xe_sessions AS s ON s.address = t.event_session_address
      WHERE s.name = N'DeprecatedFeatureDemoSession' AND t.target_name = N'ring_buffer') AS d
CROSS APPLY d.x.nodes('RingBufferTarget/event') AS e(n);

The text type, SET ROWCOUNT and the hint without WITH came back as final support events. sp_dbcmptlevel came back as an announcement, followed by two events for sysdatabases, the old system table it reads. The hint written WITH (NOLOCK) produced no event.

The client_app_name action tells you which application sent the code. Does the old code live in a procedure you own or in an application you can’t edit? The counters can’t say. This column can. Add it to the query when you capture from a shared server.

Fix What You Find

Each of the deprecated SQL Server features has a modern replacement. Use varchar(max), nvarchar(max) or varbinary(max) for text, ntext and image. Use TOP in the statement instead of SET ROWCOUNT. Write every table hint with WITH. Use ALTER DATABASE ... SET COMPATIBILITY_LEVEL instead of sp_dbcmptlevel. Replace old system tables such as sysdatabases with the catalog views, such as sys.databases.

You could argue that deprecated SQL Server features are harmless while they run. They are harmless until the version that removes them. Then the failure lands in an upgrade window, with a deadline, and nobody remembers the procedure. Finding it in a quiet week costs far less.

A compatibility level change is a separate step from an upgrade. Check the list before either one. A feature can survive a level change and still fail after a version change.

Run the session during a full business cycle, including month end, so rare code paths run. The events fire when the code compiles or runs, so a path nobody takes won’t appear. Check the plan cache and your source code for those.

What to Remember

Read the counters for a quick look, and use the session for the details. Fix the final support list first. Stop and drop the session when you finish, and then drop the demo database.

ALTER EVENT SESSION DeprecatedFeatureDemoSession ON SERVER STATE = STOP;
DROP EVENT SESSION DeprecatedFeatureDemoSession ON SERVER;
GO
USE master;
GO
IF DB_ID(N'DeprecatedFeatureDemo') IS NOT NULL
BEGIN
    ALTER DATABASE DeprecatedFeatureDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE DeprecatedFeatureDemo;
END;

Deprecated is not broken, it is a warning you can still read.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.