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
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. |