Ahmad Yaseen

Auditing by Reading the SQL Server Transaction Log

February 25, 2019 by

In the previous articles of this series, we discussed the reasons behind auditing the different SQL Server instance and database events, how to audit the SQL Server instance manually and using the Extended Events and Triggers methods. In this article, we will show how to audit your SQL Server instance by reading the content of the SQL Transaction Log file of the user database.

Transaction Log Overview

In SQL Server, the database consists of two types of files, the database data file, in which the tables data is stored, and the SQL Server Transaction Log file that is used to log the different types of data modification operations. For more information about the SQL Server Transaction Log file check the SQL Server Transaction Log Architecture article.

The events that are logged to the SQL Server Transaction Log files include the data modification operations, such as INSERT, UPDATE and DELETE statements, database tables and indexes DDL operations, such as CREATE and DROP statements, database tables truncation operations, and page allocation and deallocation operations.

Why the Transaction Log File

For many SQL Server database administrators, including me at the beginning of my working days, the SQL Server Transaction Log file was a black box that is opened only by the SQL Server Engine, with no way to dive inside it and check its content.

SQL Server Engine writes valuable information in the SQL Server Transaction Log file, that describes the different types of events performed on the SQL Server and database instance levels. This information will be logged to the SQL Transaction Log file with no additional configuration required from the database administrator side. In other words, that events information is there in the SQL transaction log file and waiting for a reader to take benefits from it.

Reading the Transaction Log File

SQL Server provides us with number of built-in functions that can be used to read the online SQL Server Transaction Log file data. In order to understand these methods, we will try to perform multiple data modification operations on a database table and use these methods to read the SQL Transaction Log file:

Inserting transactional data

fn_dblog()

fn_dblog is an undocumented built-in, system table-valued function, that can be used to read the active portion of the online SQL Server Transaction Log File. This function takes two parameters, the start and the end LSN of the log, with the NULL value for both parameters can be used to view all logs available in the active portion of the SQL Transaction Log file. For example, the below query is used to list all INSERT and DELETE operations available in the active portion of the SQL Transaction Log file:

And the result in our example will be like:

Results from fn_dblog query in SQL Server

It is recommended to filter the columns returned from the fn_dblog() function, and choose only the columns that contain the data you are interested in, as it returns 129 columns. Due to the fact that it is an undocumented function, it is not easy to know the content of the different columns for each operation type. In addition, the values of the inserted or modified rows will be viewed as hexadecimal values, which requires big effort to parse and reconstruct it to a meaningful values using the correct data types.

fn_dump_dblog()

It is a system built-in function, that can be used to read the online SQL Server Transaction Log file, in addition to the SQL Transaction Log backup files. This function takes 68 mandatory parameters that should be provided in order to read the transaction log. The T-SQL script below is used to read the online portion of the SQL Transaction Log file, by providing all default parameters, and NULL values for the start and end LSN to read all the active portion rows, as follows:

And the result of the previous query in our example, which is similar to the result returned from the fn_dblog() function, that needs big effort to parse and reconstruct it manually to make it meaningful and understandable, will be like:

Results from fn_dump_dblog query in SQL Server

DBCC PAGE

This DBCC command can be used to read the content of the database online files. To read the content of the SQL Server Transaction Log file, we need first to enable the 3604 Trace Flag, in order to be able to display the result of the DBCC command, using the command below:

Then run the DBCC command, providing the database Transaction Log file ID, which is 2 in our case, as below:

You can ignore the errors and bad headers that you will get from the command and concentrate on the hexadecimal output from the SQL Server Transaction Log file, that also needs big effort translating it to a meaningful data, as shown below:

Results from DBCC PAGE query

3rd party tools

From the previous examples, you can see clearly the effort that is required to write the proper T-SQL statement that filters the retrieved data and parse it to a meaningful view and use it as a SQL Server auditing solution. From this point, the need for a tool, that is used to easily read the SQL Transaction Log file content with the least effort and display it in a user-friendly view, is coming.

The SQL Server administration market provides us with variant number of tools, that is used to view the content of the SQL transaction log file. ApexSQL Log is a SQL Server Transaction Log reader, that can be easily used to view the content of the online SQL Transaction Log file, the detached Transaction Log file or Transaction Log backup files chain. It also reconstructs the SQL Server Transaction Log data automatically if needed, by reading the database Full backup file. And the result will be a complete and meaningful information about the performed operations, with the ability to check the old and new values, that is viewed in a user-friendly way.

ApexSQL Log can be downloaded from the ApexSQL tools download page, and installed to your machine, by following a straight-forward installation wizard. After installing the ApexSQL Log, click on the Axe icon to start the tool.

When the ApexSQL Log started, provide the name of the SQL Server instance that you need to connect to, the authentication mode and credentials that will be used to connect to the SQL Server, and finally the name of the database for which you manage to view its transaction logs, then click Next as shown below:

Select a database in a SQL Server transaction log reader

In the displayed Select data sources window, select the source from where you will read the SQL Transaction Logs. It provides you with the ability to read the database online Transaction Log file, to choose from the list of available Transaction Log backup files created for that database, or click on the Add button to include a detached SQL Transaction Log file for that database, then click Next, as shown below:

Select a datasource in a SQL transaction log reader

ApexSQL Log provides you with the ability to view the SQL Server Transaction Log file content in different ways. From the Select Output page, and for auditing purposes, we will choose to open the SQL Transaction Log content in a grid form, by selecting the Open results in grid option, as follows:

Select output in a SQL transaction log reader

The Filter Setup page provides us with variant filtering options to narrow down the transactions returned from the SQL Transaction Log file, based on the time range, the operations types, tables names and other advanced options.

If you will use ApexSQL Log as an auditing tool, you can choose the Continuous Auditing option, that writes the Transaction Log file content to a destination file. Click Finish to start reading the Transaction Log file content, as shown below:

Select a time-range filter in a SQL transaction log reader

The retrieved comprehensive information, that is displayed on the grid view, using the ApexSQL Log, will show you exactly who performed the selected change, when this operation started and finished and other useful information that can be easily used as an auditing solution, with the ability to script the selected operations for redo and undo purposes and the view the values before and after modifying the database tables data or schema:

Select a time-range filter in a SQL transaction log reader

For more information about using ApexSQL Log, see the article read a SQL Server Transaction Log.

In the next article of this series, we will show how to audit the SQL Server database tables using the Change Data capture feature. Stay tuned!

Table of contents

SQL Server Audit Overview
Implementing a manual SQL Server Audit
Creating a SQL Server audit using SQL Server Extended Events and Triggers
Auditing by Reading the SQL Server Transaction Log
Change Data Capture for auditing SQL Server
Auditing SQL Server Using Change Tracking Feature
SQL Server – Database Audit Specifications
SQL Server – Server Audit Specifications
Audit SQL Server using System Temporal Tables
Audit SQL Server Using ApexSQL Audit tool

Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen
Auditing, Transaction log

About Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

350 Views