Support
 
Support Get Quote
 
 
 
 
Database platforms

Understanding SQL Server Audit better

Read more
 
Previous article
Next article
Network devices

Critical Windows events: Event ID 6008 - Unexpected system shutdown

Read more
 
Microsoft SQL

How to read transaction logs

Feb 10, 2022 3 min read
 
  • Microsoft SQL

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

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:

SELECT * FROM fn_dblog(NULL,NULL)

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:

SELECT * FROM fn_dump_dblog
(NULL,NULL,NULL,NULL,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,DEFAULT,
DEFAULT,DEFAULT,DEFAULT,DEFAULT, DEFAULT)

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:

DBCC TRACEON (3604, -1)

DBCC PAGE ( {'Database name' | DB_ID}, File_number, Page_number [, output={0|1|2|3} ])

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.

You may also like

 

Interested in a
log management
solution?

Try EventLog Analyzer

 

 

Manage logs, comply with IT regulations, and mitigate security threats.

Seamlessly collect, monitor, and analyze
logs with EventLog Analyzer

Your request for a demo has been submitted successfully

Our support technicians will get back to you at the earliest.

  •  
  •  
By clicking 'Submit', you agree to processing of personal data according to the Privacy Policy.

  Zoho Corporation Pvt. Ltd. All rights reserved.

Link copied, now you can start sharing
Copy