Shrink Transaction Log File In SQL Server

If you are often doing data migration or data scrubbing in an SQL Server database, you will notice the transaction log file grows abnormally. After the operation is complete you may need to shrink transaction log file. There are multiple ways to shrink the database log file. (See my other article for details on shrinking data file, i.e. the mdf file.)

The easiest and safest way is to use the DBCC SHRINKFILE transact-sql method to shrink just the transaction log file without affecting the database file. Another way is to use the Shrink File GUI in SSMS. I’ll go through these methods one by one. Before shrinking the transaction log, be aware of the best practice and follow them.

Best Practice

• Take backup of the database before shrinking the transaction log.
• Avoid shrinking the database file or transaction file repeatedly. repeated shrinking may increase fragmentation.
• Shrinking will be efficient after completing a major database operation like data migration, data scrubbing, etc.
• The database require free space for its regular operation. So, even after shrinking the database, the files may increase in size. So, leave some empty unused space in the database file or transaction file for performing regular operation.

Using Query Editor (Transact-SQL)

1. To start with take a full backup of the database.
2. Take a transaction log backup using this query.
   

   Query Template

   USE {{Database Name}}
   GO
   BACKUP LOG {{Database Name}} TO DISK = '{{Backup Location URI}}'
   GO
   

   Example

   USE WideWorldImporters
   GO
   BACKUP LOG WideWorldImporters TO DISK = 'C:\Temp\WWI.trn'
   GO
   

3. Get the name of the transaction log file name using this query.
   

   Query Template

   USE {{Database Name}}
   GO
   EXEC sp_helpfile
   GO
   

   Example

   USE WideWorldImporters
   GO
   EXEC sp_helpfile
   GO
   

   
   In this example WWI_log is the name of the log file.

4. Now execute DBCC SHRINKFILE. Include the log file name in the shrink file query. The target size is optional. But it’s good to specify a target size to give a reasonable free space for regular operations.
   

   Query Template

   USE {{Database Name}}
   GO
   DBCC SHRINKFILE('{{Log File Name}}', {{Target Size in MB}})
   GO
   

   Example

   USE WideWorldImporters
   GO
   DBCC SHRINKFILE('WWI_Log', 10)
   GO
   

   

Using GUI

1. Login to SSMS.
2. In the Object Explorer, expand the Databases folder.
3. Select the database whose log file you want to shrink.
4. Right click the database and select Tasks >> Shrink >> Files.
   
5. In the Shrink File window, choose the file type Log from the File Type drop down box.
6. In the Shrink action section, select the option Reorganize pages before releasing unused space.and enter the space you want to leave free.
   
7. Click Ok.

Reference

• https://technet.microsoft.com/en-us/library/ms178037(v=sql.105).aspx