Transactional log files are important in optimization of database servers. A good DBA should have comprehensive knowledge to optimize and use the transactional log files. This area is mostly ignored by DBAs with small size databases, because their whole logs are mostly minimal as compared to massive storage and also they do not face performance issues and bottle necks.
What is in log file?
When SQL Server is functioning and operating, the database engine keeps track of almost every change taking place within the database by making entries into the transaction log so that it can be used later if required. The location of the SQL Server transaction log is configured at the same time the database is created. In SQL Server each database contains at least one data file and one transaction log file. SQL Server stores the data physically in the data file. The transaction log file stores the details of all the modifications performed to SQL Server database and the details of the transactions that performed each modification. Due to fundamental and important requirement, logging the details of the transactions cannot be turned off in SQL Server. Transaction logs can be used in a backup situation so possibly putting them on a disk other than that occupied by the primary data files may be a good idea for future use.
Structure of transaction log file
The transaction log file is logically divided into smaller segments that are referred to as virtual log files. Modifications that SQL Server makes to the size of the transaction log file, such as truncating the transaction log files or growing the transaction log files, are performed in units of virtual log files. If the transaction log file that corresponds to a SQL Server database is filled and if you have set the option for the transaction log files to grow automatically, the transaction log file grows in units of virtual log files.
Challenges with size of transactional log files
Sometimes, the transaction log file may become very large and you may run out of disk space. When a transaction log file grows until the log file uses all the available disk space and cannot expand any more,
- you can no longer perform any data modification operations on your database
- SQL Server may mark your database as suspect because of the lack of space for the transaction log expansion
Transaction log expansion may occur because of the following reasons or scenarios:
- Uncommitted transactions
- Extremely large transactions
- Operations: DBCC DBREINDEX and CREATE INDEX
- While restoring from transaction log backups
- Client applications do not process all results
- Queries time out before a transaction log completes the expansion and you receive false
- Log full’ error messages
- Unreplicated transactions
- Dealing the growing size problems
There are two main approaches to deal with the growing size problem of transactional log files.
- Reduce the size of file
- Reduce the size and shrink the file
Apparently both approaches seem same, but the importance difference is that when we just reduce the size through truncate or backup option, SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space. While in second approach after reducing the size when shrink option is used, evacuated space is shrinked and file size is reduced.
Reduce the size of the transaction logs:
To recover from a situation where the transaction logs grow to an unacceptable limit, you may truncate the inactive transactions in your transaction log. Or you may back up your transaction log file. While the backup of your transaction log files is created, SQL Server automatically truncates the inactive part of the transaction log. SQL Server reuses this truncated, inactive space in the transaction log instead of permitting the transaction log to continue to grow and to use more space. You can also delete the inactive transactions from a transaction log file by using the truncate method. In such case after you manually truncate the transaction log files, you must create a full database backup before you create a transaction log backup.
Shrink the transaction log file:
The backup operation or the Truncate method does not reduce the log file size. To reduce the size of the transaction log file, you must shrink the transaction log file. To shrink a transaction log file to the requested size and to remove the unused pages, you must use the DBCC SHRINKFILE operation. The DBCC SHRINKFILE Transact-SQL statement can only shrink the inactive part inside the log file. The DBCC SHRINKFILE Transact-SQL statement cannot truncate the log and shrink the used space inside the log file on its own.