Every now and then there is a question on forum mentioning that transaction log file has grown large, how to shrink it. This blog talks about SQL Server shrinking transaction log file. Though, if transaction log is being managed correctly need to shrink shouldn’t arise, however if need be this is how it can be done.
Don’t try this on production until necessary.
The first step is to find out how to much free space left in a transaction log file.
If we look at AdventureWorks2014 database, the log file is around 93% free. There is a difference between Truncate and shrink. When we truncate a transaction log file, the space is made with in the log file by removing the inactive portion whereas when we shrink a log file the file size is physically reduced.
Execute below query to get the filename of the log file to shrink.
sp_helpfile
Execute the below query to shrink log file.
DBCC SHRINKFILE(AdventureWorks2014_Log,2)
The above query shrinks the log to 2 MB. The function takes 2 arguments, the log file name or file id and the target_size in MB. Let’s now check the T-log size and space used by running the SQLPERF command.
The log file size of AdventureWorks2014 database is reduced to 2 MB from 49 MB.
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook
