How to audit user activities from Transaction Log File

  • Article

There are scenarios where you may want to find out which user deleted the data or created a table etc .. 

 Use below statement in the database whose transaction log file is to be audited

DBCC LOG (<DBID\DBNAME>, 3)

Replace DBID with the ID of database whose transaction log file is to be analyzed.

This may generate huge output if the transaction log file size is big

You may get lucky if the transaction log is not truncated 

Next, in the output from above query you can look for TRANSACTION SID or DESCRIPTION column for point in time LSN having LOP_BEGIN_XACT statement.

Those column usually contain the SID value for the user who made the change & can be mapped in SID column in SYSUSERS table of that database to which the log file belongs.

Also check the TRANSACTION NAME column which may contain type of operation done, as a matter of fact the DESCRIPTION column usually contains operation & SID details. For e.g.

CREATE TABLE;0x0105000000000005150000005d28f57fd53ad8354354e02a00d40400

Comments

  • Anonymous
    January 01, 2003
    Sirve pila
  • Anonymous
    October 05, 2015
    Nice explain, Is there any log analyzer tool for reading transaction log in SQL Server ?
  • Anonymous
    October 05, 2015
    There are few tools available online one of them being http://internalsviewer.codeplex.com/ .. you can also look up online for fn_dblog function in SQL Server which will help read the T-Log
  • Anonymous
    October 05, 2015
    Excellent Information Anurag and thanks for sharing Internals viewer for SQL, I have used SysTools SQL Log Analyzer. which have the capability of reading and analyzing SQL Server log files. I am not comparing it from any other log analyzer tool but in my scenario it helped me a lot.
    Try at your behalf: http://www.systoolsgroup.com/sql-log-analyzer.html
  • Anonymous
    December 03, 2015
    Sorry for the late comment here, but if you are still looking for a good log analyzer, I recommend ApexSQL Log:https://www.apexsql.com/sql_tools_log.aspx. It reads from .ldf file and let you revert changes made to your database. It features a free trial, so you can give it a try.