SQL Server Undocumented 2

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.

SQL Server Undocumented 1

SQL Server has many undocumented functions and commands. I will write series of posts with functions to have a library online.

The first, I’d like to write about %%PhysLoc%% and the function fn_PhyslocCracker

With this function, I can see my table physical location and use DBCC PAGE to see the data in each page.

SELECT *
FROM dbo.MyTable AS m
CROSS APPLY fn_PhyslocCracker(%%physloc%%) plc;

DBCC PAGE(MyDatabse, File_ID, Page_ID, Type) WITH TABLERESULTS