DBCC LOG(databasename, typeofoutput) where typeofoutput: 0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default) 1: As 0, but also retrieve any flags and the log record length. 2: As 1, but also retrieve the object name, index name, page ID and slot ID. 3: Full informational dump of each operation. 4: As 3 but includes a hex dump of the current transaction log row.fn_dblog runs in a context of a DB, so be sure to select the one that is interested to you. Results are ordered, the oldest are first to be displayed.
SELECT SPID, -- The process ID for the transaction [Begin Time], -- The start time for the transaction [Current LSN], -- The lsn of this record [Previous LSN], -- Previous lsn for this record Operation, -- The operation performed by the log record Context, -- The resource affected by the operation [Log Reserve], -- The space reserved for rollback in bytes AllocUnitName, -- The name a the affected allocation unit [Page ID], -- The page ID of the affected page [Number of Locks], -- The number of locks held by the transaction [Lock Information] -- Information about the locks held and the resources locked FROM fn_dblog(NULL, NULL)The number of rows displayed depends on a Recovery Mode, if it's set to Simple, SQL server will decide how much information keep in transaction log file, if it's set to FULL, then this query will display all operations from a time when Recovery Mode was set to FULL.