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

The part 1 of this topic is still in my old blog. It will get sometime before I have time to migrate to this blog.

In this post, I will describe the method to examine the log file to determine data integrity of damaged page.

Please note that this recovery procedure is by assuming that the log file is intact.

The first thing to do is determine the checkpoint LSN time of checkpoint before the database crash occur. Please be reminded that each step is assuming that I can change the in memory value of the starting LSN to the starting LSN before the database crash. Please consult to this post about the method to change the in memory value of LSN in DBCC LOG routine to LSN of interest.

In the sample case that I already presented in previous post, the checkpoint LSN (or the starting point LSN) before the crash occur is at 00000ec0:000006c:0006.

It is also useful to determine the time of last checkpoint information before the crash is occur :

DBCC TRACEON(3604)
DBCC LOG(edoc, 3, ‘numrecs’, 1)

The checkpoint time for this LSN is :

Checkpoint Begin
2008/01/28 21:10:31:223

This checkpoint LSN the is used to retrieve all the log records to for the affected pages to be analyzed later.

To view the log record of certain pages (m_pageId = 1:8353), and conserve memory space in my PC, I can use this command :

DBCC TRACEON(3604)
DBCC LOG(edoc, 3, ‘pageid’, ‘1:20a1’)

The second step is to write down all the m_lsn values of damaged pages. This value denotes the last LSN of log operation, seconds before the database is crashed. This is done by viewing the page in the database with emergency or suspect mode.

This is because, each time when the database is restarted, the affected page’s m_lsn value is always updated when the recovery is occured, and the m_lsn value we get may be of wrong value.

Eventhough we can not view the log in suspect mode or emergency mode, we can still use DBCC PAGE command to view the m_lsn value.

In my case, the damaged page 1:8353 has m_lsn value 3777:169:2 which translates to LSN value 00000ec1:00000a9:0002.

When consulting the log record for that particular value (using DBCC LOG Option 3), I can find that the last operation is modifying the record at slot 23.

We can find the missing record that still in the log file that is not flushed back to the database file by searching the particular page id in log record. In my case, the missing log record is from 24 to 31.

Remember that SQL server also tries to redo/rollback the database, so I can verify by checking the slot value in that page :

m_slotCnt = 32

In my case, the redo or rollback mechanism for this page is done successfully after the torn bit is manually fixed.

After that, I perform the SELECT DML language to retrieve the latest record to detect any torn pages that is occurred after this page :

rcv1

The hex value 0x3EE8000 translates to the page id 8052.

This error is not detected when the database is attached, so, I have to step back to the damaged database, stop the sql server, overwrite the database with the damaged one (after the fixed one is copied to temporary file for later reuse), restart the SQL server, and have it viewed with DBCC PAGE

DBCC TRACEON(3604)
DBCC PAGE (‘edoc’, 1, 8052, 3)

m_lsn = (3778:92:3)

The above LSN translates to 00000ec2:000005c:0003. Well, I have to go back to the fixed one, because, this database is used so that I can view the log file. Stop the server, overwrite again the database, and restart the server.

But, when attempting to instruct DBCC LOG to view this log record, I receive this error :

Server: Msg 9003, Level 20, State 1, Line 1
The LSN (3778:92:3) passed to log scan in database ‘edoc’ is invalid.
Connection Broken

Perhaps the record is really not existent any more. So, let’s check it manually. Using the formula from my previous post :

dbcc loginfo(edoc)

rcv2

m_blockOffset = 92

Log Block offset location = 770048 + (512 * 92) = 844800 = 0xC7800. By viewing this offset using hex editor :

rcv3

It is verified that the log record is exist, and because of some unknown flag in DBCC LOG checking, the function refused to decipher this record for me. So, I have to resort to the manual method to decipher it.

Please refer to my posts about the method of how to do this.

After the decipher process, I found that this record is actually performing some update operation, and it is not get written to the original table, eventhough the recovery process is done successfully on previous page, causing that page have the torn page error.

By performing the update to the original table, then I successfully make the record in synch with the record contained in the log file. And the conclusion is, there are certain cases that log record can be useful in verifying and even restoring the damaged record.

Advertisements

3 Responses to “Utilizing Log File for MS SQL Server Data Recovery (Part 2)”

  1. computer repair Says:

    computer repair…

    […]Utilizing Log File for MS SQL Server Data Recovery (Part 2) « Welcome to the Corner of Excellence[…]…

  2. dineshv Says:

    DBCC TRACEON(3604)
    Command excuted then it show result follow ,How can see result of above command

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  3. Keshto Arya Says:

    Woah! I’m really digging the template/theme of this blog. It’s simple, yet effective.
    A lot of times it’s hard to get that “perfect balance” between user friendliness and visual appeal. I must say that you’ve done a
    excellent job with this. Additionally, the blog loads extremely quick for me on
    Opera. Superb Blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: