Quick note on Microsoft SQL transaction log file
Every MS SQL Server database consists of at least two physical operating system files and these files are specified when a database is created or altered. One and possibly more (secondary files) are used for the data (as well as indexes and allocation pages) and one for the transaction log.
What is the transaction log
Basically the transaction log records changes made to the database and stores enough information to allow SQL Server to recover the database. This process takes place when a SQL Server instance is started and optionally everytime a restore is performed and it means the reconciliation of the data files and the log files: any changes to the data that the log indicates have been committed must appear in the data files, and what is not marked as committed must not appear in the data files.
How it works
Operations performing database modifications write to the transaction log describing the changes made, the data values that were added or removed, information about the transaction involved, and the date and time of the beginning and end of the transaction. In addition, the SQL Server writes log records when certain internal events happen, such as checkpoints. Each of these record is labeled with a unique log sequence number and grouped by the transaction they belong to. The server uses a Buffer Manager which guarantees that the log will be written before the changes to the database are written, using the log sequence number (write-ahead logging).
There are two types of recovery. The restart recovery runs every time SQL Server is started on each database because each database has its own transaction log. If the service was shut down cleanly with no open transactions in any database, only minimal recovery is necessary upon system restart, otherwise this can also be referred as crash recovery when an unexpected event stops the SQL Sever service. The other type of recovery, restore recovery, is run by request when a restore operation is executed.
Both of these types follow a recovery model (database level configurated) which determines how a database transaction log is mantained. The FULL recovery model provides the least risk of losing work, all operations are fully logged and SQL Server preserves the transaction log until it is backed up. This model allows the database to be restored to a specific point in time also allowing twoi new backup options besides full backup: differential (copy all the data changes since last full backup) and transaction log (copy all data changes since last full or transaction log backup) backup. The BULK_LOGGED recovery model is similar with the full recovery model, the only difference being the way the operations are minimally logged, offering more performance and least log space needed. There is also a simple recovery model: the transaction log contains only a minimal amount of information and is truncated whenever a checkpoint (controlled by server) occurs, so the database can only be restored to the exact time when a full or differential backup occurred.
One important issue regarding the transaction log is the controlling of the size and growth. The simple logging means only that the backup strategy is simple (never need to worry about log backups), however all operations are logged in simple mode also. In case of large or long-running transactions (the log cannot be truncated during active transactions) this issue must be taken into consideration.
When creating a database, there are two file options to consider, “Initial Size”, and “Auto-growth” – both can be configured from the SQL Server Management Studio accessing Database Properties.
To check the log size and space used for all databases one can use following command:
DBCC SQLPERF('logspace')
If a database is not in auto-truncate mode and log backups are not performed regular (only full backups are made), the transaction log will never be truncated. In this case if the log file fills up or runs out of physical disk space the database will stop working, so one should manually truncate the log to keep it at a manageable size.
To truncate and shrink an overgrown log file to 1MB:
USE test; DBCC SHRINKFILE(test_log, 1) BACKUP LOG test WITH TRUNCATE_ONLY DBCC SHRINKFILE(test_log, 1)
The 1st shrink command will make the SQL Server mark a shrink point in the log, but no actual shrinking will take place until log records are freed by either backing up or truncating the log. One can truncate the log in one of two ways: change the database’s recovery mode to SIMPLE or run the backup log command with truncate_only option. At this point the file size will be reduced.
On the other hand, if the log is not set to Auto-growth, to alter the size of the log file use:
ALTER DATABASE test MODIFY FILE (name=test_log, size=150MB)
Tags: database MSSQL recovery model restore SQL Server transaction log
Posted in: Databases, MSSQL
December 14, 2009
| 
Daniel Bozdoc | 
No Comments
Leave a Reply