Saturday, June 21, 2008

How to Suppress Transaction Log Checking

In MS SQL Server, Transaction Log refers to the mechanism of undo and redo the data in the database. Sometime, in case of database crash, we just one to be able to view the data as it is so the recovery step can be carefully orchestrated. In this case, we have to find some ways to suppress undo/redo mechanism, if this became issue that crop up in data recovery session.

In the event of database crash, MS SQL server will first marked the database as suspect. If we then detach the suspect database, and if crashes affect the important pages such as PFS page, then it is impossible to re-attach the database again, unless some supression of checking mechanism is to be done on that database.

Recall that in my previous post, it is possible to bypass the checking mechanism by performing 'page transplantation' from backup database. But we are in big trouble if there are no backup available, i.e. we just have the crashed database and can not do anything about it because it can not be attached to be examined carefully (i.e. by DBCC PAGE command).

The illustrated case below will explain why this kind of suppresion is important.

I was faced with crashed database with no backup available. As usual, the database is marked as suspect (greyed icon), and after some futile attempt to do the recovery in order that the database is up again, I detach the database, upon trying to attach the database again I came upon this message :



Recall that in my previous post, I already describe the way to 'fix' the torn page by examining and then synchronizing the sectors in that page. After examining each sector, it revealed that the m_tornbits (red box) value is not in synch with the rest of sectors' torn bits.



One of the torn bits of sample sector is shown here (green box) :



All of the torn bits in the end of each sectors evaluated to 0x02 where as the m_tornbits value is 0x01 which is why MS SQL server give the above error.

To get rid of this nasty torn page error that preventing me to attach the database, I change the m_tornbits value from 0x01 to 0x02, and re-attach the database again using SQL Enterprise Manager. Then, I arrived with this message :



It seems that I already bypass the torn page checking, but tripped with another checks that preventing successful attach process. Upon examining the error log :

2008-06-21 15:10:35.43 spid51 Error while redoing logged operation in database 'MBS'. Error at log record ID (2387:408:4)

So, I am faced with transaction log issue. It seems that MS SQL server is trying to redo the transaction on this page, that is already corrupted, which is fortunately, unsuccesful, because even if it is successful, it will make things more complicated. It is because we only want to check the database as it is, seconds before disaster.

In order to bypass this mechanism, now it is time to consult the 'Oracle' Kalen Delaney (Inside Microsoft SQL Server 2000) again about transaction log :

If the LSN in the page is less than that of log LSN, then the transaction should be redone. Otherwise, if LSN in the page is greater, transaction should not be redone.

This 'law' implied that it is possible to bypass the redo mechanism, because this is the exact thing I expected. By somehow change the LSN in that page to be of higher value, then by theory, it will be able to bypass the redo mechanism.

If you see the page header in the above picture, the yellow box denotes the LSN of that defected page. Hence, by changing the value of 2387 = 0x00000953 to for example 2388 = 0x00000954 (to 0x54090000 in big endian in physical file), it is possible to bypass the transaction redone mechanism. I change the LSN value, firing up the attach database dialogue box, and bingo ! The database can be attached !



Now, with database successfully attached, I can examine more closely which data is corrupted.

Happy transaction log-ing :)

4 comments:

Anonymous said...

viagra uk cheap purchase buy viagra uk cheap purchase buy free viagra in the uk pfizer viagra generic viagra cheap mail order viagra viagra rx problems with viagra how viagra works viagra buy how does viagra work viagra uk cheap purchase buy viagra pharmacy buying viagra in uk

Anonymous said...

Post98, http://www.arlo.net/massacree/ online viagra, qsis3, http://www.arlo.net/fccgb/ viagra no prescriptions, umic0, http://www.arlo.net/fccgb/notes/ buy generic viagra, nldh9, http://www.arlo.net/bytes/ cheap viagra, zaus8, http://www.arlo.net/live/ viagra sale online

Anonymous said...

Υour repοrt haѕ еstаblishеd hеlpful
tο me peгsonаllу. It’s
very eԁucationаl and you're simply obviously really well-informed in this field. You possess opened up our eyes to different opinion of this specific subject matter with intriguing, notable and strong content.
Also visit my blog post ... buy Xenical

Anonymous said...

Υour write-up featuгes verifiеd necessary to
mysеlf. It’s rеally useful anԁ you really are ceгtainly
гeally educаted of this tyρe.
You gеt opened ouг face to be able to diffeгent
opinion of thіs kind of matter ωith іntriquing, nοtable and ѕound articles.
Also visit my homepage :: Buy meridia