I would suggest you follow what the sp_blitz
recommended. You can change the option to CHEKSUM
. Checksum might be able to detect more problem that torn_page.
Torn page allows you to detect whether page was successfully written to disk or not. It would not check what is inconsistency inside the page. While checksum performs more thorough checks. Having said all this you must know there is no replacement of good backup and you must (if possible) run dbcc checkdb every day
.
What Paul said, quoting from This SQLServercentral link
The difference is Unmeasurably small - all it's doing is grabbing the
first two bits from each of the 16 sectors, storing them in the page
header, and then writing an alternating bit pattern into the two bits
in each sector. And the reverse when the page is read again. Almost
nothing compared to what a page checksum is doing by reading the whole
page and adding the contents to a 'checksum'.
However, torn-page detection does not detect corruption within sectors
- only when a write fails some way through. On 2005 onwards you should always have page checksums enabled for this extra benefit - 1-2% CPU
shouldn't be an issue (otherwise you've got other problems).
Some blog posts around this: How to tell if the IO subsystem is
causing corruptions? and Inside The Storage Engine: Does turning on
page checksums discard any torn-page protection?.
Following is what BOL document has to say
Consider the following important points when you use the PAGE_VERIFY
option: • The default is CHECKSUM.
• When a user or system database is upgraded to SQL Server 2005 or a
later version, the PAGE_VERIFY value (NONE or TORN_PAGE_DETECTION) is
retained. We recommend that you use CHECKSUM
In earlier versions of SQL Server, the PAGE_VERIFY database option is
set to NONE for the tempdb database and cannot be modified. In SQL
Server 2008 and later versions, the default value for the tempdb
database is CHECKSUM for new installations of SQL Server. When
upgrading an installation SQL Server, the default value remains NONE.
The option can be modified. We recommend that you use CHECKSUM for
the tempdb database
.
NOTE: If you enable page checksum it would not be enabled for entire pages of the databases. There is no process as such which puts this option for all pages. When a certain page would be read in memory, changed, and written back
to disk checksum would then be enabled for that page. So you would have to perform operation like index rebuild to bring as much page as possible in memory. Paul Randal has Myth section related to Checksum please read it.
Databases that are created on SQL Server 2005 and 2008 will have page checksums enabled automatically unless user changes the setting in the model database.