How to Fix Redo/RollBack in SQL Server’s Log File

Suppose we have this error in the event of attaching the database :

2008-07-30 09:16:29.03 spid53    SQL Server Assertion: File: <pageref.cpp>, line=4680
Failed Assertion = ‘rowPtr [fsLog->log_modOffset] == fsLog->log_oldValue’.
2008-07-30 09:16:29.04 spid53    Error: 3624, Severity: 20, State: 1.
2008-07-30 09:16:29.05 spid53    Error: 3313, Severity: 21, State: 2
2008-07-30 09:16:29.05 spid53    Error while redoing logged operation in database ‘MBS’. Error at log record ID (2387:408:4)..

It will be more helpful to determine the detail operation pertaining this LSN in the log file. This information then can be use to analyze the cause and extent of database file physical damages.

The log record 2387:408:4 is translated to 00000953:00000198:0004

File sequence number 2387 = 0x953 is located at offset 0x40000 = 262144

Log block sector offset = (408 * 512) + 262144 = 471040 = 0x73000

Since we can not attach the database to view the log information, log block of sector 0x73000 is cloned to Test_Log.LDF (after detached) using LSN 00000006:00000017:0001 as clone placement.

File sequence 00000006 is located at offset 0x2000 = 8192 in test_log.LDF. Hex value 0x17 = 23

Log block sector for clone placement : 8192 + (512 * 23) = 19968 = 0x4E00.

After clone process is done, re-attach the test database.

Using DBCC LOG intercept techniques for LSN 00000006:00000017:0001, the detail description of sequence 4 LSN that the SQL Server tries to redo :

Operation : LOP_SET_FREE_SPACE
Page ID : 0001:00001f98
Byte Offset : 4409
New Value : 0x44
Old Value : 0x43

Page ID 0001:00001f98 = 8088 is actually PFS Page. SQL Server finds some inconsistency in the byte value it tries to replace, hence the error occurred.

Byte offset 4409 = 0x1139, value the byte value at this offset is 0x44 which is not the same as 0x43. Change it to 0x43 will get rid of this error.

Lets examine another error :

2008-07-30 10:13:28.49 spid51    Error: 3313, Severity: 21, State: 2
2008-07-30 10:13:28.49 spid51    Error while redoing logged operation in database ‘mbs’. Error at log record ID (2387:462:3)..

Log block sector offset = (462 * 512) + 262144 = 498688 = 0x79C00

Which is another PFS page error :

Operation : LOP_SET_FREE_SPACE
Page ID : 0001:00001f98
Byte Offset : 4411 = 0x113B
New Value : 0x42
Old Value : 0x41

Byte at offset 0x113B = 0x42 which is already a new value. Turn it to 0x41 will eliminate the error and one step toward progress to have fully functional attached database.

So, in human terms, the above error actually interpreted as this : gosh, I expected that this value should be the same with my old value, but it is already the new value, let’s stop the operation and inform the user about this.

Advertisements

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: