Get dates of the MSSQL Database Transaction Log records

Posted on

Question :

I am wondering if there is a way not just to get the transaction log (e.g. using fn_dblog), but also get the date for the start of execution for each of the transactions recorded?

It’s okay to get any amount of extra uninteresting data as long as I get the date or timestamp of the transaction execution.

Answer :

Your question is not very detailed in exactly what you want, but How to read and interpret the SQL Server log has some very good information about interpreting the SQL Server log.

Specifically relating to finding the start of a transaction (from the above post):

The first think I will do will be to look at the LOP_BEGIN_XACT
operations. This log record marks the beginning of a transaction and
is very important in log analysis because is the only log record that
contains the date and time when the transaction started, and also
contains the SID of the user that had issued the statement. Here is
how I query for these operations:

select [Current LSN], [Operation], [Transaction ID], [Parent Transaction ID],
    [Begin Time], [Transaction Name], [Transaction SID]
from fn_dblog(null, null)
where [Operation] = 'LOP_BEGIN_XACT'

enter image description here

Even if it did, it would include things like backups, index rebuilds, statistics updates, etc. Not sure that’s be totally useful by itself.

Zoom out a little – what’s the problem you’re trying to solve, and there may be a better answer to a different question?

Leave a Reply

Your email address will not be published. Required fields are marked *