SQL Server – Read sql transaction Logs with ::fn_dblog

Use ::fn_dblog to read the active portion of the transaction log file. There is loads of interesting information within the Transaction Log file and it’s possible to use this information to discover , for example , the last DML operation and who dropped a table . I’ll post on this topic tommorow

The raw output from fn_dblog  is hard to understand. Attempting to reverse engineer the meaning and structure of the transaction logs is complex. The main purpose of the Transaction Logs is to support SQL Server transaction and consistency levels. If there is a failure , the Transaction Logs play a critical part in recovery

There are 3^rd party tools that map the SQL Server Transaction Log format – creating a user-friendly output.  For certain circumstances , these are very useful

Decreasing the amount of information returned in the data set makes ::fn_dblog useful. Once the data set is customised  it can offer some good clues for investigation., in recent activity on the SQL Server database.

This query retuns the columns:

Login = Originator of Transaction

Operation = Type of Transaction

AllocUnitName = Object name

Conetxt = Resource affected by the transaction

Begin Time =  Transaction start time

End Time = Transaction end time

SELECT suser_sname(convert(varbinary,"Transaction SID")) as 'Login', 
Operation,
AllocUnitName,
Context,
"Begin Time",
"End Time" 
FROM ::fn_dblog( null , null)

Read More

SQL Server – Last DML operation - SQL Server DBA

How to request SQL Server troubleshooting

SQL Server – default trace FAQ