Torn page detection

Question

Torn page detection

thinknight

SSCrazy

Points: 2068
More actions
November 13, 2008 at 9:33 am

#132083

Torn page detection is a select box in database properties tab.
What does it mean ? Why is it used for?
Does it effect performance? Do Every tempdb databases carry this check box on?
Does it directly effect tempdb database?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Every database should have this on in SQL 2000. In 2005 it's been replaced by page checksums which should be used instead.

It's a way for SQL to detect that a page has been damaged, either due to an error writing the page to disk, or a misbehaving IO system.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 2

GilaMonster (11/13/2008)
Every database should have this on in SQL 2000. In 2005 it's been replaced by page checksums which should be used instead.
It's a way for SQL to detect that a page has been damaged, either due to an error writing the page to disk, or a misbehaving IO system.

🙂 I really am such a massive fan of your Gail 🙂 You always have the answers.

Please dont get upset with me agian, I mean this with all respect. Your my Hero, err um Hero-ess :blush:

Have a g reat weekend.

Adam Zacks-------------------------------------------Be Nice, Or Leave

sivaprasad SSCertifiable Points: 6183 More actions · Answer 3

Torn I/O is often referred to as a torn page in SQL Server documentation. A torn I/O occurs when a partial write takes place, leaving the data in an invalid state. SQL Server 2000/7.0 data pages are 8 KB in size. A torn data page for SQL Server occurs when only a portion of the 8 KB is correctly written to or retrieved from stable media.

SQL Server always checks I/O completion status for any operating system error conditions and proper data transfer size and then handles errors appropriately. Torn pages generally arise after system outages where the subsystem does not complete the entire 8-KB I/O request.

Disk drive manufacturers generally limit data transfer guarantees to sector boundaries of 512 bytes, so if the stable media is a physical disk and the controller does not contain a cache with battery backup capabilities, the I/O request is limited to the final spin/transfer rate of the physical drive. Thus, if an I/O is issued to write 8 KB (for a total of sixteen 512-byte sectors), but only the first three sectors make it to stable media, then the page becomes torn resulting in data corruption. A subsequent read of the 8-KB page would bring in 3 sectors of the new version of the page and 13 sectors of an older version.

SQL Server can enable torn page detection on a per-database basis. A portion of the first 512-byte sector of the page contains the page header. When torn page detection is enabled, the header member contains information about each of the other 512-byte sectors or segments of the 8-KB page. When the page is read in, the torn page information is used to detect a torn page situation.

Torn page detection incurs minimal overhead and is a recommended practice for SQL Server installations

Starting with SQL Server 2005 database level page verify "CHECKSUM" is the default and in SQL Server 2008 it is enabled for "tempdb" too.

However "Torn page detection " is not default for SQL Server 2000. Also databases upgraded from previous version on SQL Server 2005 and 2008 will not have "Page Verify" enabled.

References: SQL Server 2000 I/O Basics

Sivaprasad S - [ SIVA ][/url]http://sivasql.blogspot.com/[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

I highly recommend you reference Books Online. A WONDERFUL resource.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

EL_oh_EL Old Hand Points: 377 More actions · Answer 5

I know this is a old thread but i have a question 🙂

So if I have databases running in SQL 2000 Compatibility mode on a SQL 2005 standard server, do i turn checksum for every database including the ones running in SQL 2000 compatibility mode?

Thanks!

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Yes.

Please in future post new questions in a new thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

EL_oh_EL Old Hand Points: 377 More actions · Answer 7

EL_oh_EL

Old Hand

Points: 377

May 25, 2011 at 2:13 pm

#1329941

Thanks!

will do! 🙂