SQL Server – Read sql transaction Logs with ::fn_dblog

04 December,2012 by Jack Vamvas

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 3rd 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


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL Server – Read sql transaction Logs with ::fn_dblog


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer