TORN PAGE DETECTION option in 2005 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

TORN PAGE DETECTION option in 2005

Does anyone have an opinion on what TORN PAGE DETECTION should be set on 2005? Below is how we set it in our original DDL to create the DB. EXEC sp_dboption N’INOW6′, N’torn page detection’, N’true’
GO Are the there any potential performance impacts of having this set or not? Thanks
tkisling
You need to consider for turn on page checksums. With this option pages are not protected in any way after the option has been enabled UNTIL they’re read in, changed, and written back out. Only at that point will they have torn-page protection or a page checksum on them. Torn page detection’s replacement-checksum and also test and verify for dbcc_checksum statement that enables extra data verification. The checksum is enabled using the SET page_verify recovery option of the alter database command. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
@http://www.askasqlguru.com/ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>