How to Shrink / Reduce the Size of the SQL Server Transaction Log?

Over time, with the use of SQL Server, the database transaction log file can grow quite large.

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 can occur only while the database is online and, also, while at least one virtual log file is free. In some cases, shrinking the log may not be possible until after the next log truncation.

“Shrinking database and log files can be set to occur automatically. However, we recommend against automatic shrinking, and the autoshrink database property is set to FALSE by default. If autoshrink is set to TRUE, automatic shrinking reduces the size of a file only when more than 25 percent of its space is unused. The file is shrunk either to the size at which only 25 percent of the file is unused space or to the original size of the file, whichever is larger.”

How Does Shrinking the Log File Work?

Shrinking the transaction log reduces its physical size by removing one or more inactive virtual log files. The unit of the size reduction is always the virtual log file. For example, if you have a 600 megabyte (MB) log file that has been divided into six 100 MB virtual logs, the size of the log file can only be reduced in 100 MB increments. The file size can be reduced to sizes such as 500 MB or 400 MB, but the file cannot be reduced to sizes such as 433 MB or 525 MB. A virtual log file that holds any active log records, that is, an active virtual log file, is part of the logical log, and it cannot be removed.

The Database Engine chooses the size of the virtual log file dynamically when log files are created or extended.

Note: Certain factors, such as a long-running transaction, can keep virtual log files active for an extended period. This can restrict log shrinkage or even prevent the log from shrinking at all.

Workaround for shrinking the transaction log

You need to take a transaction log backup which will truncate the log file and then perform shrinking of log file using DBCC SHRINKFILE statement it will be successful.

Run following T-SQL Command (script) for every database using SQL Server Management Studio.

USE <DatabaseName>
GO
DBCC SHRINKFILE(<TransactionLogName>, 1)
BACKUP LOG TO DISK = 'backup file path'
DBCC SHRINKFILE(<TransactionLogName>, 1)
GO

Here,
1. <DatabaseName> is the name of your database.
2. <TransactionLogName> is the name of your transaction log file.

You can find your transaction log file name using following T-SQL Command

SELECT name FROM sys.database_files WHERE type_desc = 'LOG'

Example:

USE MyDB
GO
DBCC SHRINKFILE(MyDB_log, 1)
BACKUP LOG MyDB TO DISK = 'F:\Backup\MyDB_TLog.trn'
DBCC SHRINKFILE(MyDB_log, 1)
GO

Note: If the transaction log fails to shrink, you will need to backup the entire database first, then try again.


<<Click here to see all posts>>


Leave a Reply

Your email address will not be published. Required fields are marked *

Prove You Are Human Time limit is exhausted. Please reload CAPTCHA.