SQL Job Agent: T-SQL Script to Shrink All Log Files on the Server

In some situations a scheduled shrink of the log files is needed to release disk space. The script bellow can be scheduled in SQL Server Agent Job.

Create database

USE [master];
GO
 
IF (DB_ID('PL') IS NOT NULL)
	BEGIN
		ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
		DROP DATABASE PL;
	END
GO
 
CREATE DATABASE PL;
GO
 
USE PL;
GO

USE [master];

IF (DB_ID('PL') IS NOT NULL)

BEGIN

ALTER DATABASE PL SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

DROP DATABASE PL;

END

CREATE DATABASE PL;

USE PL;

Create test table

CREATE TABLE PL.dbo.PL__Test__1 (ID INT IDENTITY(1, 1) PRIMARY KEY, FirstName VARCHAR(32));
GO

1 2	CREATE TABLE PL.dbo.PL__Test__1 (ID INT IDENTITY(1, 1) PRIMARY KEY, FirstName VARCHAR(32)); GO

Check Log files sizes (will be reused)

SELECT
	DB_NAME(MF.database_id) AS DatabaseName
	, MF.Name AS LogicalName
	, MF.Physical_Name AS PhysicalName
	, (MF.size*8)/1024 [Size (MB)]
	, D.recovery_model_desc AS RecoveryModelDescription	
FROM
	sys.master_files AS MF
	JOIN sys.databases AS D
		ON MF.database_id = D.database_id
WHERE MF.type_desc = 'LOG';

SELECT

DB_NAME(MF.database_id) AS DatabaseName

, MF.Name AS LogicalName

, MF.Physical_Name AS PhysicalName

, (MF.size*8)/1024 [Size (MB)]

, D.recovery_model_desc AS RecoveryModelDescription

FROM

sys.master_files AS MF

JOIN sys.databases AS D

ON MF.database_id = D.database_id

WHERE MF.type_desc = 'LOG';

Fill the log file with fake data

DECLARE @Counter INT = 1;

BEGIN TRAN
	WHILE (@Counter <= 300000)
	BEGIN
		INSERT PL.dbo.PL__Test__1 (FirstName)
		SELECT 'Peter';

		SET @Counter += 1;
	END
COMMIT TRAN

DECLARE @Counter INT = 1;

BEGIN TRAN

WHILE (@Counter <= 300000)

BEGIN

INSERT PL.dbo.PL__Test__1 (FirstName)

SELECT 'Peter';

SET @Counter += 1;

END

COMMIT TRAN

Check Log files sizes (run the script above)

And the script to shrink the log files

USE [master];

DECLARE
	@C_DatabaseName SYSNAME
	, @C_LogicalName SYSNAME
	, @C_RecoveryModelDescription SYSNAME
	, @DynamicSQL1 VARCHAR(MAX) = '';

DECLARE @ShrinkLogFileCursor CURSOR;

SET @ShrinkLogFileCursor = CURSOR FOR
	SELECT
		D.name AS DatabaseName
		, MF.Name AS LogicalName
		, D.recovery_model_desc AS RecoveryModelDescription
	FROM
		sys.master_files AS MF
		JOIN sys.databases AS D
			ON MF.database_id = D.database_id
	WHERE
		MF.type_desc = 'LOG' -- Only log files
		-- AND MF.Physical_Name LIKE 'D:\Databases\%' -- Specifed directory
		-- AND D.name = ''  -- Specifed database
		;

OPEN @ShrinkLogFileCursor;
FETCH NEXT FROM @ShrinkLogFileCursor INTO @C_DatabaseName, @C_LogicalName, @C_RecoveryModelDescription;
 
WHILE @@FETCH_STATUS = 0
BEGIN -- Cursor loop

-- USE
SET @DynamicSQL1 = 'USE ' + QUOTENAME(@C_DatabaseName) + ';
';

-- ALTER DATABASE to SIMPLE
IF (@C_RecoveryModelDescription = 'FULL')
	BEGIN
		SET @DynamicSQL1 += '
ALTER DATABASE ' + QUOTENAME(@C_DatabaseName) + '
SET RECOVERY SIMPLE;
';
	END

-- DBCC SHRINKFILE to 1 MB
SET @DynamicSQL1 += '
DBCC SHRINKFILE (' + QUOTENAME(@C_LogicalName) + ', 1);
';

-- ALTER DATABASE to FULL
IF (@C_RecoveryModelDescription = 'FULL')
	BEGIN
		SET @DynamicSQL1 += '
ALTER DATABASE ' + QUOTENAME(@C_DatabaseName) + '
SET RECOVERY FULL;
		';
	END

EXEC (@DynamicSQL1);

/*
print '
------------------------- Start -------------------------'
print @DynamicSQL1
print '-- ' + @C_DatabaseName + ' | ' + @C_LogicalName + ' | ' + @C_RecoveryModelDescription
print '------------------------- End -------------------------'
*/

FETCH NEXT FROM @ShrinkLogFileCursor INTO @C_DatabaseName, @C_LogicalName, @C_RecoveryModelDescription;

END -- Cursor loop
 
CLOSE @ShrinkLogFileCursor;
DEALLOCATE @ShrinkLogFileCursor;

USE [master];

DECLARE

@C_DatabaseName SYSNAME

, @C_LogicalName SYSNAME

, @C_RecoveryModelDescription SYSNAME

, @DynamicSQL1 VARCHAR(MAX) = '';

DECLARE @ShrinkLogFileCursor CURSOR;

SET @ShrinkLogFileCursor = CURSOR FOR

SELECT

D.name AS DatabaseName

, MF.Name AS LogicalName

, D.recovery_model_desc AS RecoveryModelDescription

FROM

sys.master_files AS MF

JOIN sys.databases AS D

ON MF.database_id = D.database_id

WHERE

MF.type_desc = 'LOG' -- Only log files

-- AND MF.Physical_Name LIKE 'D:\Databases\%' -- Specifed directory

-- AND D.name = '' -- Specifed database

;

OPEN @ShrinkLogFileCursor;

FETCH NEXT FROM @ShrinkLogFileCursor INTO @C_DatabaseName, @C_LogicalName, @C_RecoveryModelDescription;

WHILE @@FETCH_STATUS = 0

BEGIN -- Cursor loop

-- USE

SET @DynamicSQL1 = 'USE ' + QUOTENAME(@C_DatabaseName) + ';

-- ALTER DATABASE to SIMPLE

IF (@C_RecoveryModelDescription = 'FULL')

BEGIN

SET @DynamicSQL1 += '

ALTER DATABASE ' + QUOTENAME(@C_DatabaseName) + '

SET RECOVERY SIMPLE;

END

-- DBCC SHRINKFILE to 1 MB

SET @DynamicSQL1 += '

DBCC SHRINKFILE (' + QUOTENAME(@C_LogicalName) + ', 1);

-- ALTER DATABASE to FULL

IF (@C_RecoveryModelDescription = 'FULL')

BEGIN

SET @DynamicSQL1 += '

ALTER DATABASE ' + QUOTENAME(@C_DatabaseName) + '

SET RECOVERY FULL;

END

EXEC (@DynamicSQL1);

print '

------------------------- Start -------------------------'

print @DynamicSQL1

print '-- ' + @C_DatabaseName + ' | ' + @C_LogicalName + ' | ' + @C_RecoveryModelDescription

print '------------------------- End -------------------------'

FETCH NEXT FROM @ShrinkLogFileCursor INTO @C_DatabaseName, @C_LogicalName, @C_RecoveryModelDescription;

END -- Cursor loop

CLOSE @ShrinkLogFileCursor;

DEALLOCATE @ShrinkLogFileCursor;

The databases with recovery model = FULL are altered to SIMPLE, shrinked and altered back to FULL as explained in MSDN.

Check Log files sizes (run the script above)

Keep it simple :-)