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)
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
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 fullfill 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
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
there’s no performance benefit to having multiple log files . More Information:
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:
- 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.
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:
BACKUP LOG ACMEDB
TO DISK = 'C:\ACMEDB.TRN'
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
If you create a good backup strategy, you should be able to recover from a disaster very close (possibly even up-to-the-minute) to the time of the disaster. However, you are not required to do log backups. Instead you can do only database-level backups and recover with those. That’s fine. There’s really nothing wrong with that strategy. However, it does mean that you have a greater potential for data loss. Basically, if you decide that you’re doing to do weekly full backups – then you need to be OK with losing everything that’s happened since your last full backup. If that’s OK, then performing full database backups (and never worrying about the log) is absolutely fine.
However, if you want more granular control and more recovery options (again, possibly even up-to-the-minute recovery – which is transactional recovery up to the time of the disaster), then you MUST add transaction log backups into your disaster recovery strategy.
So, make this decision FIRST:
- Am I OK with some data loss? (then you’re probably OK with just database-level backups… but, you will need to do something else! be sure to keep reading!!!)
- Do I want to minimize data loss to the smallest amount possible? (then you’re going to want to AUTOMATE transaction log backups)
SQL Server is expecting YOU to manage the transaction log by backing it up. Once it’s backed up, SQL Server can remove the inactive records from the transaction log (and when you do a transaction log backup, it automatically clears the inactive records by default).
So, there are really two choices – and ONLY two choices here:
- Perform transaction log backups as part of your maintenance plan
- Change the recovery model to the SIMPLE recovery model so that SQL Server clears inactive transactions from the log automatically
Shrinking the Transaction Log
If you know that a transaction log file contains unused space that you will not be needing, you can reclaim the excess space by reducing the size of the transaction log. This process is known as shrinking the log file . Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files.
The shrink operation is not a good maintenance practice because it doesn’t solve the transaction log size issue permanently. After the initial shrinking, the transaction log will grow again. As the auto-growth event is one of the most intensive SQL Server operations, it should be avoided
Data file shrink should never be part of regular maintenance, and you should NEVER, NEVER have auto-shrink enabled
data file shrink also generates a lot of I/O, uses a lot of CPU, and generates *loads* of transaction log – as everything it does is fully logged.
Do Not Rebuild Index After Shrink, Because :
One person shrinks the database to gain space (thinking it will help performance), which leads to increase in fragmentation (reducing performance). To reduce the fragmentation, one rebuilds index, which leads to size of the database to increase way more than the original size of the database (before shrinking). Well, by Shrinking, one did not gain what he was looking for usually.