How to read transaction logs?

What are transaction logs?

Transaction logs contain information about all the transactions carried out in your SQL Server. Transaction logs are stored in a single LDF file in the default database folder C:\Program Files\Microsoft SQL Server\MSSQL. Each transaction log entry has a unique Log Sequence Number (LSN) associated with it. The transaction log file contains huge volume of data and manually analyzing it is a complicated process.

Reading transaction logs

Microsoft hasn't provided any dedicated tool to view, monitor and audit SQL Server transaction logs. However, there are a few methods to read your transaction logs. They are

• fn_dblog()
• fn_dump_dblog()
• DBCC PAGE command.

Reading transaction logs using fn_dblog()

The fn_dblog() function, when called in a database allows you to view the online and active transaction logs. This function takes two parameters that specify the LSN of the first and last log entries to be retrieved. If you want to view all the active and online logs, you can simply mention (NULL, NULL) as parameters in the fn_dblog() function.

The function can be executed as follows:

This function returns a table with 129 columns by default.

Reading transaction logs using fn_dump_dblog()

The fn_dump_dblog() allows you to view online and backup transaction logs. It takes up 63 parameters and all of them have to be specified to execute this function. You can use DEFAULT for the parameters to obtain all the transaction logs.

This function can be executed as follows:

This function also returns a table with 129 columns by default.

An advantage of using fn_dump_dblog() is that it allows you to restore your database to a specific point in time using the WITH STOPBEFOREMARK statement. This enables the DB admins to rectify transaction errors or undo changes that affect the database performance.

Reading transaction logs using Database Consistency Checker (DBCC) PAGE

The DBCC page command is used to look into any page in SQL Server. You have to enable the trace flag 3604 that helps in redirecting an output to a result window to view transaction logs using this command. The DBCC page command to view transaction logs can be as follows:

This command displays a page of the transaction logs which is still difficult to interpret and analyze.

Are you monitoring your transaction logs efficiently?

The methods mentioned above are all undocumented by Microsoft and you can use them at your own risk. Viewing and analyzing transaction logs through fn_dblog() and fn_dump_dblog() functions are difficult as both these functions return 129 columns by default. This data has to be further drilled down using queries to obtain relevant and necessary information. It becomes a practical difficulty for DB admins to view, interpret and analyze the transaction logs to identify anomalies. Auditing transaction logs isn't efficient using these methods. Instead, you can use a log management solution to read and audit transaction logs.

EventLog Analyzer is a log management solution that can collect, parse, index and analyze your transaction logs. It generates comprehensive reports on SQL Server transaction logs which includes Transaction log backup report. Real-time alerts can be configured for anomalous activities taking place in your SQL instance using EventLog Analyzer. This notifies the DB admins through SMS and email about a potential threat or an attack. Check out EventLog Analyzer's SQL Server auditing capabilities.