One more undocumented function/commands. The fn_dump_dblog function is used to read transaction logs from a log backup file.
This is an example fo the undocumented function to read transaction logs:
SELECT [Current LSN], [Operation], [Transaction Name], [Transaction ID], SUSER_SNAME ([Transaction SID]) AS DBUser
FROM fn_dump_dblog (
NULL, NULL, N'DISK', 1, N'C:\temp\Backup_Logfile.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT)
A good example to use it is when I tried to restore up to specific LSN using the command
RESTORE LOG WITH STOPBEFOREMARK = ‘lsn:’;
To read your current log file the function fn_dblog is used. For more information about this function click here
Follow bellow the operations is returned for the function:
OPERATION |
DESCRIPTION |
LOP_ABORT_XACT |
Indicates that a transaction was aborted and rolled back. |
LOP_BEGIN_CKPT |
A checkpoint has begun. |
LOP_BEGIN_XACT |
Indicates the start of a transaction. |
LOP_BUF_WRITE |
Writing to Buffer. |
LOP_COMMIT_XACT |
Indicates that a transaction has committed. |
LOP_COUNT_DELTA |
? |
LOP_CREATE_ALLOCCHAIN |
New Allocation chain |
LOP_CREATE_INDEX |
Creating an index. |
LOP_DELETE_ROWS |
Rows were deleted from a table. |
LOP_DELETE_SPLIT |
A page split has occurred. Rows have moved physically. |
LOP_DELTA_SYSIND |
SYSINDEXES table has been modified. |
LOP_DROP_INDEX |
Dropping an index. |
LOP_END_CKPT |
Checkpoint has finished. |
LOP_EXPUNGE_ROWS |
Row physically expunged from a page, now free for new rows. |
LOP_FILE_HDR_MODIF |
SQL Server has grown a database file. |
LOP_FORGET_XACT |
Shows that a 2-phase commit transaction was rolled back. |
LOP_FORMAT_PAGE |
Write a header of a newly allocated database page. |
LOP_HOBT_DDL |
? |
LOP_HOBT_DELTA |
? |
LOP_IDENT_NEWVAL |
Identity’s New reseed values |
LOP_INSERT_ROWS |
Insert a row into a user or system table. |
LOP_LOCK_XACT |
|
LOP_MARK_DDL |
Data Definition Language change – table schema was modified. |
LOP_MARK_SAVEPOINT |
Designate that an application has issued a ‘SAVE TRANSACTION’ command. |
LOP_MIGRATE_LOCKS |
|
LOP_MODIFY_COLUMNS |
Designates that a row was modified as the result of an Update command. |
LOP_MODIFY_HEADER |
A new data page created and has initialized the header of that page. |
LOP_MODIFY_ROW |
Row modification as a result of an Update command. |
LOP_PREP_XACT |
Transaction is in a 2-phase commit protocol. |
LOP_SET_BITS |
|
LOP_SET_BITS |
Designates that the DBMS modified space allocation bits as the result of allocating a new extent. |
LOP_SET_FREE_SPACE |
Designates that a previously allocated extent has been returned to the free pool. |
LOP_SORT_BEGIN |
A sort begins with index creation. – SORT_END end of the sorting while creating an index. |
LOP_SORT_EXTENT |
Sorting extents as part of building an index. |
LOP_UNDO_DELETE_SPLIT |
The page split process has been dumped. |
LOP_XACT_CKPT |
During the Checkpoint, open transactions were detected. |