Thursday, July 3, 2008

Utilizing Log File for MS SQL Server Data Recovery (Part 1)

In my previous posts, I already explain the method to enable the crashed database to be attached, especially crashed database caused by torn page error.

The second phase (or second part) after this, is to examine the crashed data in terms of pages that is damaged or broken. For example, pages that has torn page error that we already 'fixed' in order that database can be attached.

Please remember that what we do at this phase is only to fix the torn bits so that SQL server do not trip on this error, but the integrity of the data is still questionable. Now the question is, is it possible to correct, or at lease determine the causes of page damage ? In this post, I will describe how it is possible and the restrictions about this issue.

The context of data recovery phase that I am about to explain is applied to MS SQL Server 2000 only, but this knowledge can also be expanded to be applied as a starting point for MS SQL Server 2005.

First, the restrictions. The restrictions I am talking about is by assuming the integrity of the MS SQL Server's LOG data is intact. As we already know, MS SQL Server technology is using the write-ahead method when writing the database, i.e. the system first write to the log file and ensure that the log file (LDF) is written successfully before applied to the actual database (MDF).

Using this assumption, theoretically, by using the database log file it is possible to view the modification that is about to be done/already done to the database seconds before the system crashed.

So, in order to determine which data is valid in the event of database crash, or particularly the page damage, I am relying on the log file as the reference point of operation that is about to be or already done to actual MDF file, seconds before disaster. Or in short, I am relying to the log file as the only valid data to be checked against the data in MDF file.

To present the sample case, I will have some quick review of the steps that I already elaborated on previous post, and then go on to second phase of data integrity checking. In the process of the sample case walkthrough, I am also present some caveat or issue that is emerged, and unless this issue is resolved, I can not go to next step.

Usually, the surfaced issue is about the design of MS SQL Server 2000, for example, the inability to view the log file when the database mode is in emergency mode or bypass mode. I am hoping that, this will be resolved in the next version of MS SQL Server.

First, I am trying to attached the damaged database file (MDF). And I am facing with torn page error :

Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x00000000012000 in file 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\edoc_data.mdf'.


I am recording this offset, so I can examine it later against the database log file. After fixing the tornbits (m_tornbits), and re-attached it :

Server: Msg 1813, Level 16, State 2, Line 1
Could not open new database 'edoc'. CREATE DATABASE is aborted.
Device activation error. The physical file name 'D:\Program Files\Microsoft SQL Server\MSSQL\data\eDoc_Log.LDF' may be incorrect.


This is the first issue that is I am talking about in MS SQL Server 2000. I tripped this error because, the damaged database is located at D: drive, whereas the server that the database I am about to be examine is using C: drive as location for the database. There are two ways to correct this issue, either create the exact location in D: drive in destination server, or using utility such as hex editor to change the drive in MDF file, when we have no D: drive in destination server.

After resolving the above error and re-attach again :

Server: Msg 823, Level 24, State 2, Line 1
I/O error (torn page) detected during read at offset 0x00000004142000 in file 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\edoc_data.mdf'.


This is the second issue pertaining to MS SQL Server 2000. Actually, the server is trying to redone or rollback, depends on transaction condition at the time of crash. But, alas, the page is already damaged and have the torn page checking enabled, this again, will tripped the torn page error, unless something has to be done to fix the m_tornbits value.

Again, I am fixing the m_tornbits value, record the damaged offset (0x4142000) for later examination, and re-attach the database. This time, the database can be successfully attached with the messages below :

604 transactions rolled forward in database 'edoc' (26).
0 transactions rolled back in database 'edoc' (26).


Now, it's time to examine the damaged page(s) using the log file.

DBCC TRACEON(3604)
dbcc log(edoc, 1)


Instead of providing me with the log data before the database is crashed, I have only practically empty log data to be worked with :

00000ec1:0000011a:0001 LOP_BEGIN_CKPT
00000ec1:0000011a:0002 LOP_MODIFY_ROW
00000ec1:0000011b:0001 LOP_END_CKPT


It seems that every time the database is detached, then re-attached, the server is resetting the position of log recording, using the term 'Checkpoint'.

By using option 2 of DBCC LOG command, it is possible to view the previous LSN value :

DBCC TRACEON(3604)
dbcc log(edoc, 2)


For example, the previous LSN for 00000ec1:0000011a:0001 is 00000ec1:00000117:0003. So, is it possible, using DBCC LOG to view the previous LSN ?

DBCC TRACEON(3604)
dbcc log(edoc, 2, 'lsn', '0xec1:117:3')


Nope, it can't. This became the third issue of MS SQL Server 2000. It can only filter the lsn from the last checkpoint onward, as proved by this statement :

DBCC TRACEON(3604)
dbcc log(edoc, 2, 'lsn', '0xec1:11a:2')


It will give :

00000ec1:0000011a:0002
00000ec1:0000011b:0001


This is also to prove that the above syntax to view the previous LSN is not a garbage one, but a valid syntax. The lsn option in DBCC LOG is clearly not intended to be used to view any lsn. So, there is no starting lsn option in DBCC LOG.

Also, because of this kind of checkpointing mechanism, the information about the last checkpoint before the crash is also lost :

DBCC TRACEON(3604)
DBCC PAGE ('edoc', 1, 9, 3)


dbi_checkptLSN
--------------
m_fSeqNo = 3777 m_blockOffset = 282 m_slotId = 1


Converting the above value to hex value yield 0xEC1:11A:1 which is equal to the first record of log above. So, what is the checkpoint value at the time of crash ? Is it the same with previous LSN recorded in the active portion of log shown above ? (i.e. 00000ec1:00000117:0003).

To get those value and to prove it, I am marking the current database to emergency mode :

Sp_configure 'allow updates', 1
Reconfigure with override


Marking the database as emergency mode or bypass mode :

update master..sysdatabases set status = 32768 where name = 'edoc'

After this, I am shutting down the MS SQL Server, re-copy the database from damaged one, and restart the server. This will bypass the recovery and allow me to view the data as it is :

2008-07-03 17:01:50.80 spid8 Bypassing recovery for database 'edoc' because it is marked BYPASS.

DBCC TRACEON(3604)
DBCC PAGE ('edoc', 1, 9, 3)


dbi_checkptLSN
--------------
m_fSeqNo = 3776 m_blockOffset = 108 m_slotId = 6


Again, by converting the above value to hex equals 00000ec0:000006c:0006 which is NOT equals to 00000ec1:00000117:0003.

Now, is it possible, in emergency mode, to view the log from this checkpoint onward ?

DBCC TRACEON(3604)
dbcc log(edoc, 2)


Again, nope, it is impossible :

2008-07-03 16:31:01.98 spid52 Cannot do a dbcc log() on a database in emergency mode

This, if I am not mistaken, this will be the fourth issue in MS SQL Server 2000. In my opinion, the reason behind this error, is that in emergency or bypass mode, the log data is not initialized, and any attempt to view the log file is irrelevant :

DBCC TRACEON(3604)
DBCC DBTABLE(edoc)


LogTruncMgr @0x19FF10E8
-----------------------
m_replLSN = (0:0:0) m_ckptLSN = (0:0:0) m_oldActXact = (0:0:0)
m_backupLSN = (0:0:0) m_oldestBackupXactLSN = (0:0:0)


So, I have the dead end situation. In order to breaktrough this barrier, I have to go back to the previous fixed database, and this will initialized the log, and make the DBCC LOG operation to read the intended LSN (i.e 00000ec0:000006c:0006).

To make this post short, this is done by breaking to the DBCC LOG routine using WinDBG, and changing the in memory value of the current LSN to the LSN to be read. The detail of how this is done will be the subject of my next post.

Before the re-copying to set the database to emergency mode, I already make a backup copy so that I can step back. For stepping back, I reset the database to online mode, shut down the server, re-copy the database of previous step, and restart the server.

After that, firing the WinDBG, attach the sqlservr.exe program, and break into the DBCC LOG, setting the in memory of LSN to be read to 00000ec0:000006c:0006 and voila ! I have the log file for crosschecking to the damaged MDF file.

For example, by converting the error page at offset 0x4142000 to m_pageId = 1:8393, which is in hex value is 1:20A1, I can track the log file, looking for any operation at those page, which is, subject of my next posting.

So, keep in touch for my next two posting :)

22 comments:

Alexis said...

For work with sql files I often use-recovery database sql.On next reasons: tool solved all my problems with sql files quickly and easy.Moreover it was for free and program demonstrated repair data from corrupted databases in the MS SQL Server format (files with the *.mdf extension).

Alex said...

There are a lot of tools which works with sql server and files too. But this morning no one of it couldn't help me. But how lucky that I called my friend up and he advised me - ms sql database repair,the software solved my issue for minutes and without payment as far as I kept in mind. Moreover to my mind it is quite easy to use in like situations.

Anonymous said...

Do You interesting of [b]Female use of Viagra[/b]? You can find below...
[size=10]>>>[url=http://listita.info/go.php?sid=1][b]Female use of Viagra[/b][/url]<<<[/size]

[URL=http://imgwebsearch.com/30269/link/buy%20viagra/1_valentine3.html][IMG]http://imgwebsearch.com/30269/img0/buy%20viagra/1_valentine3.png[/IMG][/URL]
[URL=http://imgwebsearch.com/30269/link/buy%20viagra/3_headsex1.html][IMG]http://imgwebsearch.com/30269/img0/buy%20viagra/3_headsex1.png[/IMG][/URL]
[b]Bonus Policy[/b]
Order 3 or more products and get free Regular Airmail shipping!
Free Regular Airmail shipping for orders starting with $200.00!

Free insurance (guaranteed reshipment if delivery failed) for orders starting with $300.00!
[b]Description[/b]

Generic Viagra (sildenafil citrate; brand names include: Aphrodil / Edegra / Erasmo / Penegra / Revatio / Supra / Zwagra) is an effective treatment for erectile dysfunction regardless of the cause or duration of the problem or the age of the patient.
Sildenafil Citrate is the active ingredient used to treat erectile dysfunction (impotence) in men. It can help men who have erectile dysfunction get and sustain an erection when they are sexually excited.
Generic Viagra is manufactured in accordance with World Health Organization standards and guidelines (WHO-GMP). Also you can find on our sites.
Generic [url=http://viagra.opuskali.ru]Viagra Super Active[/url] is made with thorough reverse engineering for the sildenafil citrate molecule - a totally different process of making sildenafil and its reaction. That is why it takes effect in 15 minutes compared to other drugs which take 30-40 minutes to take effect.
[b]what is maximum dose of viagra
colleagues viagra
optional health care viagra
The Side Effects Of Viagra
compared levitra viagra
viagra from canada legitimate
lotensin viagra
[/b]
Even in the most sexually liberated and self-satisfied of nations, many people still yearn to burn more, to feel ready for bedding no matter what the clock says and to desire their partner of 23 years as much as they did when their love was brand new.
The market is saturated with books on how to revive a flagging libido or spice up monotonous sex, and sex therapists say “lack of desire” is one of the most common complaints they hear from patients, particularly women.

Anonymous said...

Howdy,

When ever I surf on web I never forget to visit this website[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url]Lots of good information here eka-siswanto.blogspot.com. I am sure due to busy scedules we really do not get time to care about our health. Here is a fact for you. Research shows that almost 50% of all USA adults are either fat or overweight[url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips].[/url] Hence if you're one of these people, you're not alone. In fact, most of us need to lose a few pounds once in a while to get sexy and perfect six pack abs. Now the question is how you are planning to have quick weight loss? [url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips]Quick weight loss[/url] is not like piece of cake. Some improvement in of daily activity can help us in losing weight quickly.

About me: I am author of [url=http://www.weightrapidloss.com/lose-10-pounds-in-2-weeks-quick-weight-loss-tips]Quick weight loss tips[/url]. I am also health trainer who can help you lose weight quickly. If you do not want to go under hard training program than you may also try [url=http://www.weightrapidloss.com/acai-berry-for-quick-weight-loss]Acai Berry[/url] or [url=http://www.weightrapidloss.com/colon-cleanse-for-weight-loss]Colon Cleansing[/url] for effective weight loss.

Anonymous said...

hi every person,

I identified eka-siswanto.blogspot.com after previous months and I'm very excited much to commence participating. I are basically lurking for the last month but figured I would be joining and sign up.

I am from Spain so please forgave my speaking english[url=http://bolatesttrends.info/].[/url][url=http://doworlddiscovery.info/].[/url][url=http://dicoolnewideas.info/].[/url]

Anonymous said...

Thanks for finally talking about > "Utilizing Log File for MS SQL Server Data Recovery (Part 1)" < Liked it!

Look into my web-site ... buy followers on twitter

Anonymous said...



my page: buy facebook likes indonesia

Anonymous said...

Veгy soоn this sitе wіll bе famous amid all blogging people, due to it's good articles

Look at my site: health care articles

Anonymous said...

Good ԁay! Τhis is κinԁ
of off topic but I need sοme helρ from an еѕtablished blog.
Is it νery hard tо ѕet up your own blog?
І'm not very techincal but I can figure things out pretty fast. I'm thinking аbout
making my οwn but I'm not sure where to begin. Do you have any tips or suggestions? With thanks

Have a look at my webpage :: private health insurance

Anonymous said...

Hello! I understand this is sort of off-topic however I had to
ask. Does building a well-established website such as yours take a massive amount work?
I'm completely new to blogging but I do write in my diary on a daily basis. I'd like to
start a blog so I will be able to share my own
experience and thoughts online. Please let me know if you have any ideas or tips for brand new aspiring bloggers.
Appreciate it!

Check out my web page; coconut oil for hair

Anonymous said...

You ought to take part in a contest for one of the greatest websites on the web.
I most certainly will recommend this website!

Feel free to surf to my web blog :: download 7zip

Anonymous said...

I would like to thank you for the efforts you've put in penning this website. I really hope to check out the same high-grade content from you in the future as well. In truth, your creative writing abilities has inspired me to get my own site now ;)

Also visit my web site: permanent hair straightening

Anonymous said...

Hi all, here every one is sharing such know-how, therefore it's good to read this webpage, and I used to pay a visit this blog everyday.

Feel free to surf to my blog post - diaper rash home remedies

Anonymous said...

bookmarked!!, I really like your website!


Visit my homepage: tattoo Removal cream Reviews

Anonymous said...

Way cool! Some extremely valid points! I appreciate you penning this write-up
plus the rest of the site is also very good.


Also visit my blog :: refinishing hardwood floors

Anonymous said...

Right now it appears like Movable Type is the
preferred blogging platform out there right now. (from what I've read) Is that what you are using on your blog?

My webpage: book of rah

Anonymous said...

Excellent article. I am dealing with some of these issues as well.
.

Check out my weblog: Microsoft Office Gratuit

Anonymous said...

Hello, this weekend is pleasant designed for me, for the reason
that this occasion i am reading this enormous informative post here at my residence.


Here is my webpage - Psn Code Generator - http://Www.Youtube.com/ -

Anonymous said...

Excellent blog! Do you have any tips for aspiring writers?
I'm planning to start my own website soon but I'm a little
lost on everything. Would you advise starting with a
free platform like Wordpress or go for a paid option?
There are so many choices out there that I'm completely confused .. Any ideas? Thanks a lot!

My web site: Dragon City Hack :: Http://Www.Youtube.Com/ ::

Anonymous said...

constantly i used to read smaller content which also clear their motive,
and that is also happening with this paragraph which I am reading here.


Feel free to surf to my web page sizzling hot 199 games

Anonymous said...

I read this post fully concerning the comparison of most recent and preceding technologies, it's remarkable article.

my web site: Codes Psn Gratuit

Anonymous said...

adult web cam dating http://loveepicentre.com/map/ luxembourg men dating free patents online
apostolic safe online dating [url=http://loveepicentre.com/faq/]who is deanna pappas dating now[/url] dating in davie
dating ex girlfriend's friend [url=http://loveepicentre.com/taketour/]good aspects of online dating sites[/url] free indianapolis dating services [url=http://loveepicentre.com/user/Lena123456/]Lena123456[/url] dating in the workplace players