Thursday, January 3, 2013

Reading from transaction log file - SQL Server

Sometimes I need to figure out when a certain operation happened, like when a row was deleted, or added. In order to investigate I use two tools:
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.
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.

No comments: