Shrink all SQL Server databases transaction log files

PROBLEM:
You want to shrink all SQL Server databases transaction log files to clean up some space

SOLUTION:
Use the following script to shrink all databases transaction log files. You can use it also as a maintenance job using SQL Server Agent

DECLARE @Log_name VARCHAR(1000)
DECLARE @Db_name VARCHAR(1000)

DECLARE @Recovery_model_desc VARCHAR(1000)

DECLARE @SQL nvarchar(2000)

DECLARE @ParmDefinition nvarchar(1000)

DECLARE @SizeAfter int

DECLARE db_cursor CURSOR FOR

SELECT

       F.NAME AS [LOG_NAME],

       DB.NAME AS [DB_NAME],

       DB.RECOVERY_MODEL_DESC AS [RECOVERY_MODEL_DESC]

FROM

       MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB

       ON DB.DATABASE_ID = F.DATABASE_ID

WHERE F.FILE_ID=2 AND DB.NAME <> 'tempdb'

OPEN db_cursor 

       FETCH NEXT FROM db_cursor INTO @Log_name, @Db_name, @Recovery_model_desc

       WHILE @@FETCH_STATUS = 0 

       BEGIN 

              SET @SQL = N'

              ALTER DATABASE '+ @Db_name + N' SET RECOVERY SIMPLE WITH NO_WAIT

              SELECT F.SIZE

              FROM MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB

              ON DB.DATABASE_ID = F.DATABASE_ID

              WHERE F.NAME = ''' + @Log_name +''' AND DB.NAME =  ''' + @Db_name+'''

              '

              SET @SQL = @sql +'

              USE [' + @Db_name + ']

              DBCC SHRINKFILE (' + @Log_name + ', 10)';

             

              SET @sql = @sql + N'

              SELECT F.SIZE

              FROM MASTER.SYS.MASTER_FILES F INNER JOIN MASTER.SYS.DATABASES DB

              ON DB.DATABASE_ID = F.DATABASE_ID

              WHERE F.NAME = ''' + @Log_name +''' AND DB.NAME =  '''+ @Db_name+'''

              '

             

              SET @SQL = @SQL + N'

              ALTER DATABASE ' + @Db_name + N' SET RECOVERY ' + @Recovery_model_desc + ' WITH NO_WAIT

              '

              SET @ParmDefinition =N'@Size int OUTPUT';

              EXECUTE sp_executesql @SQL ,@ParmDefinition,@Size = @SizeAfter OUTPUT;

              FETCH NEXT FROM db_cursor INTO @Log_name, @Db_name, @Recovery_model_desc

       END 

CLOSE db_cursor

DEALLOCATE db_cursor

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!