‘Extending’ the DBCC LOG Functionality

If you use the undocumented DBCC LOG command, usually this command only will retrieve the value of log file from the last checkpoint onward.

In certain cases, we may want to view log record other than or the previous one before the latest checkpoint data.

Current DBCC LOG command in SQL Server 2000 version do not have this option (i.e. to view the log record of any LSN BEFORE the latest checkpoint. For example, consider this log data :

DBCC TRACEON(3604)
DBCC LOG(TEST, 1)

Current LSN
00000006:00000018:0001

Previous LSN
00000006:00000015:0001

It is not possible to retrieve the log record for the LSN 00000006:00000015:0001. The LSN option supplied with DBCC LOG can only filter the LSN AFTER the last checkpoint LSN, which is 00000006:00000018:0001.

You have to resort to intercept the DBCC LOG routine using debugger such as WinDbg, change the passed LSN parameter value to the LSN we want to view, and resume the execution.

To accomplish this, we have to locate the routine that handle the DBCC LOG command, which is done by DumpLog::Execute routine. The portion that pass the LSN value is located at :

:008C21D6 8B4B38                  mov ecx, dword[ebx+38]
:008C21D9 8D433C                  lea eax, dword[ebx+3C] ;;class LSN (starting LSN)
:008C21DC 6A00                    push 000
:008C21DE 50                      push eax ;;class LSN
:008C21DF E818D5B7FF              call 0043F6FC {public: void __thiscall LogIter::StartScan(class LSN const &,class LogDataReadAhead *)}  {public: void __thiscall LogIter::StartScan(class LSN const &,class LogDataReadAhead *)}

Using WinDbg, you can perform the breakpoint at this address (depends on which executable image you use) 008C21DE. The eax register contains the value of passed LSN, usually this is the last checkpoint LSN :

0:023> d ds:eax
0023:02ecf434  06 00 00 00 18 00 00 00-01 00 42 00 5c f4 ec 02

To change the LSN value, for example to 00000006:00000015:0001, you can use the e command in WinDBG :

0:023> e 0023:02ecf434
0023:02ecf434 06 06
06
0023:02ecf435 00 00
00
0023:02ecf436 00 00
00
0023:02ecf437 00 00
00
0023:02ecf438 18 15
15
0023:02ecf439 00

After that, resume execution by using g option. You can go back to the Query Analyzer (the interface you use for invoking DBCC LOG command to view the result :

extend1

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: