How To Shrink All database Log Files Together

 Today while restoring 100 database in one shot using my script we found drive having log files is left with very less space . As it was test server we decide to shrink log files of all databases even though we know its not a good practice, we didn't left with other option. But shrinking all db is a laborious task and we didn't had much time to write whole new script.

Luckily in morning while answering one of questions on TechNet I saw script from Prashanth Jayaram from cognizant on same. It made my work very easy, So I blogged this script here. Thanks to Prashanth for sharing  his script with SQL DBA's

 SCRIPT :

There are 2 Scripts

1. Script to generate Query to shrink Log file
2. Script to Directly Shrink Log file (This will take care if my DB is offline )

/**********************************************/

/******Script to generate Query to shrink Log file******/

/**********************************************/

set nocount on  

SELECT 

      'USE [' + d.name + N']' + CHAR(13) + CHAR(10) 

    + 'DBCC SHRINKFILE (N''' + mf.name + N''' , 0, TRUNCATEONLY)' 

    + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 

FROM 

         sys.master_files mf 

    JOIN sys.databases d 

        ON mf.database_id = d.database_id 

WHERE d.database_id > 4 and mf.type_desc = 'LOG'

/**********************************************/

/**********************************************/
/******Script to Directly Shrink Log file *************/
/**********************************************/

  DECLARE @DBName AS NVARCHAR(100),

          @LogFileName AS NVARCHAR(100),
          @exec_stmt nvarchar(625)
       SET NOCOUNT ON
       -----------------------------------------------------------------------------
      --create the temporary table to hold the log file names
      -----------------------------------------------------------------------------
      CREATE TABLE #logfiles
      (
          dbname NVARCHAR(100),
          filename NVARCHAR(100),
      )
    -----------------------------------------------------------------------------
      --select all dbs, except for system dbs
      -----------------------------------------------------------------------------
      DECLARE curDBName CURSOR FOR
      SELECT
          [name]
      FROM
          master.sys.databases
      WHERE
          name NOT IN ('master', 'tempdb', 'model', 'msdb')
          AND state_desc = 'ONLINE'
      -------------------------------------------------------------------------------
      --add the log file name to the temporary table,groupid=0 is for logfile and 1 for datafile.
      -----------------------------------------------------------------------------
      OPEN curDBName
      FETCH NEXT FROM curDBName INTO @DBName
      WHILE @@FETCH_STATUS = 0
      BEGIN
          SELECT @exec_stmt = 'INSERT INTO #logfiles
           SELECT ''' + @DBName + ''' , name FROM ' + quotename(@DBName, N'[') + N'.dbo.sysfiles
           WHERE groupid = 0'
          EXECUTE (@exec_stmt)
      FETCH NEXT FROM curDBName INTO @DBName
      END
      CLOSE curDBName
      DEALLOCATE curDBName
      --SELECT * FROM #logfiles
      ------------------------------------------------
      --select all log filenames from the #logiles
      -------------------------------------------------
      DECLARE curLogName CURSOR FOR
      SELECT
          dbname, [filename]
      FROM
          #logfiles
      ----------------------------------------------------
      --shrink all log files
      -----------------------------------------------------
      OPEN curLogName
          FETCH NEXT FROM curLogName INTO @DBName, @LogFileName
      WHILE @@FETCH_STATUS = 0
      BEGIN
          SELECT @exec_stmt = ' USE ' + quotename(@DBName) + N' DBCC SHRINKFILE (' + quotename(@LogFileName)+')'
          --print (@exec_stmt)
          EXECUTE (@exec_stmt)
      FETCH NEXT FROM curLogName INTO @DBName, @LogFileName
      END
      CLOSE curLogName
      DEALLOCATE curLogName
      ----------------------------------------
      --clean up the logfile table
      ----------------------------------------