Ivan Stankovic

A beginner’s guide to SQL Server transaction logs

February 11, 2014 by

What is a transaction log?

A transaction log is a file – integral part of every SQL Server database. It contains log records produced during the logging process in a SQL Server database. The transaction log is the most important component of a SQL Server database when it comes to the disaster recovery – however, it must be uncorrupted. After each database modification – transaction occurrence, a log record is written to the transaction log. All the changes are written sequentially

What does a SQL Server transaction log store?

A transaction log stores every transaction made to a SQL Server database, except some which are minimally logged like BULK IMPORT or SELECT INTO. Internally it is split into the smaller parts called Virtual Log Files (VLFs). When one VLF becomes full logging continue to write into the next available in the transaction log. The transaction log file can be represented as a circular file. When the logging reaches the end of the file it starts again from the beginning, but only if all the requirements has been met and the inactive parts has been truncated. The truncation process is necessary to mark all inactive parts so they can be used again and overwritten

A log record is no longer needed in the transaction log if all of the following are true:

  • The transaction of which it is part has committed
  • The database pages it changed have all been written to disk by a checkpoint
  • The log record is not needed for a backup (full, differential, or log)
  • The log record is not needed for any feature that reads the log (such as database mirroring or replication) [1]

Logical log is an active part of the transaction log. A Log Sequence Number (LSN) identifies every transaction in the transaction log. The MinLSN is the starting point of the oldest active transaction in the online transaction log

SQL Server transaction log structure

Can SQL Server database work without a transaction log?

No, that is not possible due to the SQL Server design and ACID compliance. ACID stands for atomicity, consistency, isolation, and durability. All the transactions must fullfil these characteristics:

  • An atomic transaction is either fully completed, or is not begun at all
  • A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state
  • When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time
  • A transaction is durable meaning that once it has been successfully completed, all of the changes it made to the system are permanent [2]

Can one SQL Server database have more than one transaction log?

Yes, that is possible, but it’s only recommended in the specific situations. Adding multiple transaction log files will not improve performance of the SQL Server database in any way. Writing can occur only into one file at the time, so parallel I/O operations are not possible

Having multiple transaction log files is recommended only if the first one is full or the disk drive is running out of space. Either way, these problems should be addressed earlier and handled by creating transaction log backups and monitoring available space on the disk drive

Monitoring disk space used by SQL logs

Why is the SQL Server transaction log growing?

Every transaction is followed by logging into the online transaction log. During SQL Server work, the transaction log grows if changes are made into a database, therefore maintaining the transaction log is crucial for proper SQL Server operation

There are three recovery models available in SQL Server, depending on which one is used transaction log growth is manifested differently:

  • Simple recovery model – the transaction log backups are not supported. The truncation process is automated and space is reclaimed for reuse. There is data loss risk because changes since the most recent database backup are exposed. In the Simple recovery there is little chance for the transaction log growing – just in specific situations when there is a long running transaction or transaction that creates many changes
  • Bulk-logged recovery model – the transaction log backups are supported and needed on regular basis. There is no automated process of transaction log truncation, the transaction log backups must be made regularly to mark unused space available for overwriting. Bulk-logged recovery model reduces transaction log space usage by using minimal logging for most bulk operations
  • Full recovery model – the transaction log backups are supported and needed on regular basis. There is no data loss risk under the normal circumstances. There is no automated process of transaction log truncation, the transaction log backups must be made regularly to mark unused space available for overwriting. In the Full recovery there is biggest chance of the transaction log growing because all transactions are logged

How to maintain a transaction log in SQL Server?

A transaction log maintenance is important task in SQL Server administration. Monitoring is recommended on daily basis or even more often is a SQL Server database has high amount of traffic. The transaction log space can be monitored by using the DBCC SQLPREF command:

Monitoring the SQL transaction log space by using the DBCC SQLPREF command

  • Database Name – Name of the database for the log statistics displayed
  • Log Size (MB) – Current size allocated to the log. This value is always smaller than the amount originally allocated for log space because the Database Engine reserves a small amount of disk space for internal header information
  • Log Space Used (%) – Percentage of the log file currently occupied with transaction log information
  • Status – Status of the log file. Always 0 [3]

The transaction log should be backed up on the regular basis to avoid the auto growth operation and filling up a transaction log file. Space in a transaction log can be truncated (cleared) through SQL Server Management Studio by selecting Transaction Log as backup type or through CLI by executing the following command:

That backed up space can be reused again and it’ll be overwritten with new transactions. Some operations can’t be combined and must be executed separately:

Do I need SQL Server transaction log backups?

Yes, that is one of the most important resource when it comes to disaster recovery. They are not needed (and available) only if the Simple recovery model is used – but there is data loss exposure. Majority of Database Administrators uses 15 minutes interval or even less for a high traffic SQL Server databases. The transaction log backups are important because when taken they mark inactive VLFs that can be used for writing down new transactions

References
[1] Understanding Logging and Recovery in SQL Server
[2] ACID properties
[3] DBCC SQLPERF (Transact-SQL)

Ivan Stankovic

Ivan Stankovic

Ivan is a SQL Server professional and computer geek with years of IT and SQL Server experience. He has startedwith playing computer games, continued with computer programming and system administration. His areas of expertise are SQL Server disaster recovery, auditing, and compliance

View all posts by Ivan Stankovic
Ivan Stankovic
9 comments
NebojsaMaric
NebojsaMaric

Hi MSIS,

Transaction log files are not part of any file group, but separate files - you can see more on the topic at this link https://msdn.microsoft.com/en-us/library/ms189563.aspx

Log files are by default created in this location:
<disk>:\SQL Servers\Your MSSQLVersion\MSSQL\DATA\
While transaction log backups are created here by default:
<disk>:\SQL Servers\Your MSSQLVersion\MSSQL\Backup\

MSIS
MSIS

Ivan,

Great blog. A simple question from a newbie: just where are these t-logs saved? Is it to a file in the primary group, maybe one

in the secondary group?

Thanks.

NebojsaMaric
NebojsaMaric

Hi John,


The reason why your log file grows so large during the rebuild process is probably due to the fact that "in specific situations when there is a long running transaction or transaction that creates many changes", log file in simple recovery model will grow.

Regarding your second questions, if your database is in the simple recovery model - you will have almost no value in your transaction log files, since these are overwritten very often - so they will not hold information for the recovery.


My suggestion would be to switch your database to the full recovery model, and to take regular transaction log backups (and store them on some network storage), to ensure you have some options for the recovery.


As an alternative, you can opt to create full database backups, and then to use ApexSQL Recover (http://www.apexsql.com/sql_tools_recover.aspx) to extract only table structure from those backups, in case of a disaster, simply run the tool and extract the database structure. You can see more on the topic in the following article http://solutioncenter.apexsql.com/how-to-restore-specific-tables-from-a-sql-server-database-backup/

jv_oz
jv_oz

Hello Ivan,


This is a great post, thank you.  


We are running a SQL Server datawarehouse. It is not currently backed up because the backup happens at the same time as the DW refresh and it causes problems when run together. Acknowledging that there is an issue there that needs to be addressed, I have a question regarding logging and restores.


What I want is to be able to restore the DW structure and stored procedures in the event of failure, not the data itself as there is no persistent data. I could do a complete rebuild of the DW from the source in about 24 hours. 


The log file for the DW grows from 25Gb to almost 400Gb when we do a complete refresh and this almost takes up the entire hard disk. Recovery mode is set to Simple.


So my first question is how do I stop the log file from growing so large during the rebuild when I don't need the data itself to be able to be restored?


The second question is if the server crashed and we did not have the DW database backed up, but did have the log files backed up, would we be able to recover at least everything other than the data from those logs?


Finally, would you expect that we should be able to backup the log files independently of the database itself?


Thanks again for a great, well explained post.


John

NebojsaMaric
NebojsaMaric

Diana, if the database is in the simple recovery model, then creating transaction log backups is not possible/supported, and creating full database backups will not bring wanted results, since you will not have t-log information included in full backups, and you cannot predict at which point transaction log data will be overwritten, hence the simple recovery model is generally not recommended due to its limitations since the data is overwritten and hence 'lost'.

Your t-log growth at increased rate may be caused by the size of transactions that occur on your database, but most likely it is the drawback of the shrinking process.


Also, you may re-consider if regular shrinking is the best solution for your database since this is generally not recommended as a pre-scheduled recurring job due to the massive repercussions caused by the process. Massive fragmentation of all indexes (resulting in the performance downgrade of database queries), shrunken files will inevitably grow again, big disk fragmentation (file system fragmentation), the process of page allocation will be written as an activity in the transaction log file - which can result in massive transaction log file growth, subsequent shrinking and transaction log files growth will slow down database startup as well as restore and replication time performance degradation...

Diana Carballo
Diana Carballo

I did not understand, at the end, if my database it's in simple mode, will I need do the backup every 15 minutes? I'm confused, I have a database in simple mode, but the transaction low grows at the double of the DATA size, why? The backcup its made every day, and a Shrink its made every day too at nights 

Arjun
Arjun

Nice post Ivan - simple and direct.