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
Create test table
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';

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
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;
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 :-)