Post reply

Avoiding torn pages

  • May 10, 2004 at 11:43 pm

    #102005

    Was wondering if anyone knew what store procedure/job can be run on a daily basis that can avoid/repair/notify of a torn page or ensure that data integrity is maintained in a SQL database?

     

    Thanks

  • May 10, 2004 at 11:53 pm

    #505905

    I don't think you can avoid torn pages however, you can turn on the Database Setting called "Torn Page Detection" which will notify you of any torn pages.

    As for repairing torn pages, you will have to restore the Database!

    Kindest Regards,

  • May 10, 2004 at 11:55 pm

    #505906

    Yes I had it turn on, but as you pointed out there is no way of fixing them if they happen apart from restoring from back up. Lost a whole day yesterday!

     

    Thanks for that 

  • May 11, 2004 at 1:50 pm

    #506040

    I also had an issue fairly recently with torn page in a DB.  Usually they are a result of a hardware failure, such as the hard drive array controller in our instance.  To try and prevent this from happening again I have verified that all of our production SQL boxes NOW have controllers with battery backup.  Our situation was caused by a machine crash.  During the crash the controller was not able to finish a write due to loss of power.  Since it did not have a battery backup as soon as it lost power it just stopped. Thus leaving us with a half write and the torn page.  

    Some recovery options do exist.  There was enough of a reason for me to persue data recovery instead of just going to the last restore.  I was able to get all table data with the exeption of the data in the table where the torn page resided.  I tried everything I could think of, and eventually came up with this process.  

    1: Restore DB to my local SQL instance from the backup prior to the crash

    2: Stop SQL services

    3: Rename the recently restored MDF and LDF  to enable copy of the corrupt MDF and LDF to be attached

    4: Copy the corrupt MDF and LDF into the directory where SQL expected the files to be found for check pointing

    5: Restart SQL Service  ( This gets the corrupt database attached but marked as "suspect")

    6: Execute the following T-SQL statements from query analyzer to manipulate the status of the suspect database:

                --  allows changes to be made to system tables

                EXEC sp_configure 'allow updates', 1

                RECONFIGURE WITH OVERRIDE

                GO

     

                --  sets database in emergency mode

                UPDATE SYSDATABASES SET STATUS=32768 WHERE NAME='DBNAME'

                --   hit refresh on the  in in SQL Enterprise manager and waited for the database status to change

                     to  (Emergency Mode) 

     

                --   forces the status of the database  and the log to be ok 

                UPDATE SYSDATABASES SET STATUS=1048579 WHERE NAME='DBNAME'

                UPDATE SYSDATABASES SET STATUS2=1097794 WHERE NAME='DBNAME' 

                --   hit refresh on the  DB in in SQL Enterprise manager and waited for the database status to change

                     to normal status

     

    7: Ran DTS package to copy all table data from the corrupt database to a new empty database

    This allowed me access to my data.  The DTS package failed on the table with the torn page, but all other data was recovered.

Viewing 4 posts - 1 through 3 (of 3 total)

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