Use SQL Server Transaction Logs to Improve Database Performance

Understanding how transaction logs is an important aspect of using SQL Server. Every change to the objects contained in your database is recorded in the SQL Server’s transaction logs. That makes the logs the logical first stop when troubleshooting a problem with your database. Unfortunately, the logs can also be the source of the performance trouble, but the information in that log is intended to help identify and prevent problems.

Microsoft MSDN documentation for the SQL Server 2014 Transaction Log lists five primary purposes for the logs:

1. Recovery of individual transactions
2. Recovery of all incomplete transactions when SQL Server is started
3. Rolling a restored database, file, filegroup, or page forward to the point of failure
4. Supporting transactional replication
5. Supporting high availability and disaster recovery solutions: AlwaysOn Availability Groups, database mirroring, and log shipping

The documentation also points out that truncation is a common source of problems related to transaction logs. You have to truncate the logs to prevent them from filling available memory. Truncation involves deleting inactive virtual log files to free up space for the physical transaction log. If your database is set to the “Simple” recovery model under Options, the transaction log is truncated each time the database is successfully backed up. If you database recovery model is not set to “Simple” then you should manually backing up you transaction log. The most likely cause of a transaction log that you can not truncate is a long-running transaction (such as index maintenance).

If you need more information about how to handle transaction logs, you should read this post.

In this example, you see a log entry for three concurrent transactions: one with two insert and one delete operations; one with an insert operation that is rolled back so there’s no corresponding delete operation; and one transaction with two deletes in one transaction.

The log stores the operations as they occur. The log sequence number (LSN) determines the order in the log of the “concurrent” operations. You can query the contents of the current transaction log with this simple query:

SELECT * FROM fn_dblog(NULL, NULL)

You can even query the transaction log for specific operations:

SELECT * FROM fn_dblog(NULL, NULL) WHERE [Transaction Name] = ‘DROPOBJ’

In this article by Thomas LaRock, you an learn more about this “undocumented” function in SQL Server.