SA0216 : The TORN_PAGE_DETECTION option of ALTER DATABASE is deprecated. Use the PAGE_VERIFY option instead

Introduction

The use of the deprecated TORN_PAGE_DETECTION option can lead to compatibility issues in future versions, as this feature may no longer be supported, potentially affecting the reliability of page-level integrity checks.

Description

Using the TORN_PAGE_DETECTION option in ALTER DATABASE statements is a deprecated practice within SQL Server. This removes the option to ensure only supported features are utilized.

For example:

-- Example of deprecated usage
ALTER DATABASE SampleDB SET TORN_PAGE_DETECTION ON;

This example is problematic because TORN_PAGE_DETECTION will be removed in future SQL Server versions, making the code less future-proof and potentially leading to compatibility issues.

Future SQL Server versions may not support this option, causing errors in database maintenance scripts.
Relying on deprecated features can lead to difficulties when upgrading SQL Server, necessitating code refactoring.

How to fix

Avoid using deprecated syntax by replacing TORN_PAGE_DETECTION with the PAGE_VERIFY option in ALTER DATABASE statements.

Follow these steps to address the issue:

1.Identify any instances where TORN_PAGE_DETECTION is used in your SQL Server scripts and codebase.

2.Replace TORN_PAGE_DETECTION with the corresponding PAGE_VERIFY option to ensure compatibility with future SQL Server versions.

3.Update the SQL script to utilize PAGE_VERIFY TORN_PAGE_DETECTION instead of SET TORN_PAGE_DETECTION ON .

4.Thoroughly test the updated scripts in a development environment to verify that they function correctly before deploying to production.

For example:

-- Corrected query using PAGE_VERIFY
ALTER DATABASE SampleDB SET PAGE_VERIFY TORN_PAGE_DETECTION;

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Additional Information

Deprecated Database Engine Features in SQL Server 2017

Example Test SQL

ALTER DATABASE AdventureWorks2012
SET SINGLE_USER, TORN_PAGE_DETECTION ON,PAGE_VERIFY TORN_PAGE_DETECTION

Example Test SQL with Automatic Fix

ALTER DATABASE AdventureWorks2012
SET SINGLE_USER, PAGE_VERIFY TORN_PAGE_DETECTION,PAGE_VERIFY TORN_PAGE_DETECTION

Analysis Results

	Message	Line	Column
1	SA0216 : The TORN_PAGE_DETECTION option of ALTER DATABASE is deprecated. Use the PAGE_VERIFY option instead.	2	17

Analysis Rules