What is SQL Server Transaction Log?

Before we dive into SQL transaction log forensics , let's uderstand what a T-log file is in real. Just like many other RDBMSs, MS SQL Server also follows ‘Write-Ahead Logging’ methodology. It means all the transactions are written to log file before committing and it holds records of all the changes made to a database. For SQL forensics purposes, it's beneficial for users to know that logically, transaction logs are categorized into a few smaller parts known as VLFs or Virtual Log Files.

Every SQL database uses more than one VLF and each of them must have a minimum size of 512 KB. The size and number of virtual files in the log is evolving as the log is changing its size. When one log file is filled with transaction details then, transactions are written to the next available file. Here, we're going to perfrom SQL server forensics on these files only. MDF(primary database file) is not included here.

Basically, the log files are represented in a circular form so that if one file reaches its maximum limit then, it begins again from the starting point. SQL Server uses truncation process to mark the end of file or any unused part of log file so that it can be utilized to store the information. It's not difficult for users to perfrom SQL transaction log forensics if the right technique is known.

Forensic Analysis Working Principle Log in SQL Server

Whenever SQL Server is told to do something with the help of query that is written in Structured Query Language syntax, the internal query optimizer of SQL Server checks the query, executes it, and retrieves the required information off of the disk. In case of retrieval query, the database is streamed to requesting client across the network. But, with modification query, it modifies the data pages in memory. Users must be aware of this for executing SQL server forensics carefully.

It does not write these modifications directly to the disk; well, not yet. So, what SQL Server does is it writes the logical transaction entries in the transaction log file with .ldf filename extension where all transaction records are executed. Eventually, after few seconds, SQL Server decides to write the modified pages out to the disk. While doing this, it navigates back to the transaction log and ‘checks off’ the transaction, which made the modifications.

This means the changes are done and been written to the disk. Thus, while performing SQL Server recovery, it goes directly to the transaction log search for uncommitted transactions or those that have not yet been checked off. SQL Server reads those transactions out of log then, re-executes them and quickly writes the affected database pages to the disk. In SQL transaction log forensics, this is one crucial aspect to understand.

Analyse the Default Location of SQL Log Files

Open SQL Server Management Studio and hit a right-click on the database. Select Properties

In the newly prompted window, click on Files menu and it will show the saving location of database files along with the saved name

Use fn_dblog() Function for SQL Transaction Log Forensics Investigation

The fn_dblog() function also known as the DBCC command is one of the various undocumented functions for MS SQL Server. It allows to view the transaction log records in the active part of a transaction log file for the current database. The fn_dblog() necessitates the following parameters to be passed:

• The starting log sequence number (LSN). Also, one specify NULL that means it will return everything from the start of the log.
• The ending log sequence number. Also, one can specify NULL that means users want to return everything to end of the log.

The fn_dblog() is fairly simple and below is how to use this function to get info from the transaction log:

SELECT * FROM fn_dblog(NULL, NULL)

Now, fn_dblog will return all the transaction details so, select the transactions to analyze. The transaction results include Current LSN, performed operation, Transaction ID, Parent Transaction ID, Time, Transaction Name, and Transaction SID.

Steps to Forensically Analyze SQL Server Transaction Log Details

1. Launch Automated Tool and Click on Open to add the .ldf file.

2. The tool offer two options to add file Online DB Option and Offline DB Option. If Online DB Option is selected then, the tool will allow to choose Server Name by clicking on drop down list. Select the Authentication mode. Hit drop-down arrow to Select Database and click OK

3. The software will start scanning LDF files and after this Scanning completed successfully wizard will pop up. Click on the OK button.

4. The tool display preview of transactions. Select the desired Tables to preview and analyze the corresponding operation log entries. It sort the transactions on the basis of Login Name, Time, Table Name, and Transaction Name. Click Export button.

5. You can apply export filters, Date Filter accordingly to export the transaction records of a particular date range. You have option to export database in either SQL Database or as csv. Click Export to save records.

Conclusion

After learning SQL transaction log forensics tactics, we're at the end now. This technical page comprises a complete information on how to forensically investigate SQL Server transaction logs, including their location and working procedure. Apart from all this, we also have disclosed two different ways to examine the details of transaction logs of SQL Server.

The fn_dblog functioning helps to detect all the performed transactions. However, if users are finding the manual method complex, lengthy, and time-taking then, a professional solution is also provided here. It is one of the safest solutions to get adequate results.