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 ASNVARCHAR(100),
          @exec_stmt nvarchar(625)
       SET NOCOUNT ON
       -----------------------------------------------------------------------------
      --create the temporary table to hold the log file names
      -----------------------------------------------------------------------------
      CREATE TABLE #logfiles
      (
          dbname NVARCHAR(100),
          filenameNVARCHAR(100),
      )
    -----------------------------------------------------------------------------
      --select all dbs, except for system dbs
      -----------------------------------------------------------------------------
      DECLAREcurDBName CURSOR FOR
      SELECT
          [name]
      FROM
          master.sys.databases
      WHERE
          name NOTIN ('master', 'tempdb', 'model', 'msdb')
          ANDstate_desc = 'ONLINE'
      -------------------------------------------------------------------------------
      --add the log file name to the temporary table,groupid=0 is for logfile and 1 for datafile.
      -----------------------------------------------------------------------------
      OPENcurDBName
      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
      CLOSEcurDBName
      DEALLOCATEcurDBName
      --SELECT * FROM #logfiles
      ------------------------------------------------
      --select all log filenames from the #logiles
      -------------------------------------------------
      DECLAREcurLogName CURSOR FOR
      SELECT
          dbname,[filename]
      FROM
          #logfiles
      ----------------------------------------------------
      --shrink all log files
      -----------------------------------------------------
      OPENcurLogName
          FETCHNEXT FROMcurLogName 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
      CLOSEcurLogName
      DEALLOCATEcurLogName
      ----------------------------------------
      --clean up the logfile table
      ----------------------------------------