How to Shrink SQL Server Transaction Log Files using DBCC SHRINKFILE

Sometimes SQL Server logs files acquire a large space on the disk. In that case, we need to reduce the size of the log file to claim some space. But truncating or reducing database log file in SQL Server 2008 can be quite difficult. It also has its own issues.

This article contains a set of commands to Shrink Log Files of SQL Server database.

Advertisement

Shrink SQL Server Transaction Log Files

First of all, check the actual name of log files stored in SQL server records using the following SQL query. Execute these queries in using SQL Server management studio query browser.

USE DATABASE_NAME;
GO

SELECT FILE_NAME(2);
GO

Now, use the DBCC SHRINKFILE SQL command to shrink log files. The database must be running in SIMPLE recovery mode. Open the query windows in SQL Server management studio and run the following command. You

Make sure to change DATABASE_NAME with actual database name and LOG_FILE_NAME with log file name found in the above step. The below example query will shrink the log file to 1000MB.

USE DATABASE_NAME;
GO

ALTER DATABASE DATABASE_NAME
SET RECOVERY SIMPLE;
GO  

DBCC SHRINKFILE (LOG_FILE_NAME, 1000);
GO  

ALTER DATABASE DATABASE_NAME
SET RECOVERY FULL;
GO  

All done. Now check the size of the log files, I hope you will get the expected results.