Shrink SQL log files

SQL Server

Depending on the configuration of you databases, the log files (*.ldf) can be very large. See Selecting a Recovery Model.

Should I continue reading?

If your databases are configured to use the FULL recovery model, the log files are very large and you need some free space, you can use this SQL script to shrink them. So keep on reading 🙂

The script will change the recovery model for a single database to SIMPLE and shrink the log files. At the end it will restore the recovery model to the setting it has been before.

Just copy the script to a new Query window in your SQL Management Studio. Replace the @databaseName and run the script by hitting F5. You can also change the size in MB the log file should be shrunken to.

USE [master]
GO
DECLARE @debug int
DECLARE @databaseName nvarchar(255)
DECLARE @logfile nvarchar(255)
DECLARE @newFileSize nvarchar(5) -- in MB
DECLARE @sql nvarchar(4000)
DECLARE @parmDefinition nvarchar(500)
DECLARE @recoveryModel nvarchar(10)

--
-- CHANGE HERE BEGIN
set @debug = 1
set @newFileSize = 100 -- in MB
set @databaseName = 'your database name'
-- CHANGE HERE END
--

IF @debug = 1 PRINT 'DatabaseName=' + @databaseName

-- get recovery model from database
SET @sql = N'SELECT @model = recovery_model_desc FROM sys.databases WHERE [name]=''' + @databaseName +''''
SET @ParmDefinition = N'@model nvarchar(255) OUTPUT';
EXECUTE sp_executesql @sql, @ParmDefinition, @model = @recoveryModel OUTPUT;
if @debug = 1 PRINT 'Recovery model=' + @recoveryModel

-- set recovery modell to simple
SELECT @sql = 'ALTER DATABASE [' + @databaseName + '] SET RECOVERY SIMPLE'
IF @debug = 1 PRINT @sql
EXECUTE sp_executesql @sql

-- shrink log file
-- get logfile from database
SET @sql = N'USE [' + @databaseName + ']; SELECT @logfileName = [name] FROM sys.database_files WHERE [type_desc]=''LOG'''
SET @parmDefinition = N'@logfileName nvarchar(255) OUTPUT';
EXECUTE sp_executesql @sql, @parmDefinition, @logfileName = @logFile OUTPUT;
if @debug = 1 PRINT 'Logfile Name=' + @logfile

SELECT @sql = 'USE [' + @databaseName + ']; DBCC SHRINKFILE ([' + @logfile + '] , ' + @newFileSize + ')'
IF @debug = 1 PRINT @sql
EXECUTE sp_executesql @sql

-- restore recovery modell
SELECT @sql = 'ALTER DATABASE [' + @databaseName + '] SET RECOVERY ' + @recoveryModel + ''
IF @debug = 1 PRINT @sql
EXECUTE sp_executesql @sql

-- done