Select Page

Quick Scan Report – Using obsolete TORN_PAGE_DETECTION

Obsolete TORN_PAGE_DETECTION: There are 3 options since SQL Server 2005 for page verification. These options are used when pages are being read from or written to disk to confirm that what is in the page is a match to what was intended to be in the page.

  • None – no checking at all.
  • TORN_PAGE_DETECTION – The only option prior to SQL Server 2005.
  • CHECKSUM – Introduced in SQL Server 2005.

Obsolete TORN_PAGE_DETECTION

Since SQL Server 2005, including SQL Server 2008, 2008R2, 2012, 2014 and 2016, the CHECKSUM option is considered the most reliable option to use.

TORN_PAGE_DETECTION, although it is still available is not considered as reliable, and is generally considered obsolete.

You can get to the settings to change the Page Verify option by right clicking on a database in SSMS, then choosing Properties and clicking on the Options tab. From there the Page Verify option is in the recover section.

obsolete TORN_PAGE_DETECTION

It is recommended that this be set to CHECKSUM rather than TORN_PAGE_DETECTION or NONE.

If you want to make this change with TSQL rather than with the dialogs, you can do the following:


ALTER DATABASE [YourDatabaseName] SET PAGE_VERIFY CHECKSUM  WITH NO_WAIT;

When you make this change it changes the page verification option for the database, however the pages are not changed to CHECKSUM verification until the next time they are written.

Obsolete TORN_PAGE_DETECTION and databases upgraded from SQL Server 2000.

If you have a database that existed on a version of SQL Server older than 2005, such as SQL Server 2000, or older, when you upgrade SQL Server, the upgrading process does not automatically adjust the page verify option, and it is generally left at None, or at TORN_PAGE_DETECTION. If you are upgrading form SQL Server 2000 to a new version of SQL Server, one of your final upgrade steps should be to set the page verify option to CHECKSUM.

See Also:

Need Help

Stedman Solutions, the provider of the Database Health Monitor Application offers consulting solutions, and can help with getting your Page Verification options set up correctly, or any other database administration issues you may have.

Stedman Solutions for help TempDB only has a single data file