Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    9

    Unanswered: Torn Page Question

    We received the following errors:

    1) Error: 823, Severity: 24, State:
    2) I/O error (torn page) detected during read at offset 0x0000001455a000..erc

    Network team thinks this was caused by low disk space. I was running a database shrink last night, and someone had transferred a file to server prior to my job that took 90% of disk. My shrink job failed and then 20 minutues later we satrted to recieve the two above mentioined.

    Can a failed shrink job - which failed due to low disk space cause a torn page to occur? I have not heard of this...

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    It's not very likely but possible. Try to keep any extra pairs of hands out of the box, otherwise you're gonna lose your battles (possibly your job, hopefully not )

    You can also set Torn Page Detection on db properties.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    I found this Q & A on and IFAQ @ MS



    Q. Does enabling the "torn page" database option add any measurable performance overhead to a server?

    A. The torn page option does not add much CPU cost at all, but it can increase contention on "hot" pages. With torn page detection off, a page can be accessed while it is being written to disk. This is not true if torn page detection is on.
    Q. When does SQL Server check for torn pages? At startup? Any time it reads a page from disk?

    A. Torn page detection is done whenever a page is read from disk. In practice, this is likely to be during recovery, because any page on which the write did not complete during normal operations is very likely to be read by recovery (except for non-logged operations, such as index creation, bcp, and so on).
    Q. What happens when SQL Server detects a torn page?

    A. When a torn page is detected, a severe I/O error is raised. This error will close the connection. The database is only marked suspect if the torn page is detected during recovery.
    Q. How can I recover from torn pages?

    A. Restoring the database from a backup and rolling the transaction log forward should correct the problem with no data loss.
    Q. What situations are most likely to cause torn pages?

    A. Lab tests have shown that torn pages are quite likely to happen when disk writes are occurring and power is lost. If you do not have a battery backup or uninterruptible power supply (UPS), you should consider enabling this option.


    this doesnt mean that it cant happen from a dirty read but it doesnt say anything specific about that. it is conceivable that the shrink was not capable of moving the data to a empty space on the disk.
    however i would also look into voltage issues on the disk controllers such as a bad connector or old building wiring.

    just my 2 cents

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    That's what I am talking about!

    But...if you dig too hard you may reach the other side of the globe...then what?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •