How to shrink Microsoft SQL log files

When having Microsoft SQL databases, its log files can grow quite a bit, potentially slow down the database server and eat up disk space.

To shrink a database, one can run the following line:

EXEC D_ShrinkDBLogs 0,100,1000,'with truncate_only','DB_NAME'
(change DB_NAME with the database’s name)

To shrink all databases, one can use “sp_MSforeachdb” which is an undocumented sql stored procedure:

EXEC sp_MSForEachDB 'D_ShrinkDBLogs 0,100,1000,''with truncate_only'',''?'''

To run this, the following stored procedure need to be installed. It can just be copy pasted from the code below (or download from this link):

CREATE PROC [dbo].[D_ShrinkDBLogs]
  @target_percent TINYINT = 0, @target_size_MB INT,  @max_iterations INT, @trunc NVARCHAR(1000),
  @DB VARCHAR(30)
AS 
  SET nocount ON
  SET @trunc = 'with truncate_only'
  --SET @trunc = ''
  DECLARE 
      @last_row INT, @log_size DECIMAL(15,2), @unused_at_start DECIMAL(15,2),
      @unused DECIMAL(15,2), @shrinkable DECIMAL(15,2), @iteration INT, @file_max INT,
      @file INT, @fileid VARCHAR(5)

  SELECT @iteration = 0
  PRINT @DB
  CREATE TABLE #loginfo
     (id INT IDENTITY, FileId INT, FileSize NUMERIC(22,0), StartOffset NUMERIC(22,0),
       FSeqNo INT, Status INT, Parity SMALLINT, CreateLSN NUMERIC(30) )

  CREATE TABLE #logfiles
     (id INT IDENTITY(1,1), fileid VARCHAR(5) NOT NULL )
  INSERT #logfiles
    ( fileid )
     SELECT CONVERT(VARCHAR,fileid) FROM sysfiles WHERE status = 1048642 
     SELECT @file_max = @@rowcount

  IF OBJECT_ID('shrinktab') IS NULL 
    EXEC ('create table shrinktab ( x nchar(3000) not null )')
  INSERT #loginfo
     ( 
      FileId,FileSize,StartOffset,FSeqNo,Status,
      Parity,CreateLSN )
      EXEC ('dbcc loginfo (' + @DB + ')'
     )
  SELECT @last_row = @@rowcount
  PRINT @last_row
  SELECT 
      @log_size = SUM(FileSize) / 1048576.00,
      @unused = SUM(CASE WHEN Status = 0
      THEN FileSize ELSE 0 END) / 1048576.00,

  @shrinkable = SUM(CASE WHEN id < @last_row - 1 AND Status = 0
          THEN FileSize
          ELSE 0
          END) / 1048576.00
          FROM #loginfo

  SELECT @unused_at_start = @unused -- save for later
  SELECT 'iteration' = @iteration, 'log size, MB' = @log_size, 
  'unused log, MB' = @unused, 'shrinkable log, MB' = @shrinkable,
   'shrinkable %' = CONVERT(DECIMAL(6,2), @shrinkable * 100 / @log_size)
  WHILE @shrinkable * 100 / @log_size > @target_percent
    AND @shrinkable > @target_size_MB
    AND @iteration < @max_iterations
    BEGIN
      SELECT @iteration = @iteration + 1 -- this is just a precaution
      EXEC ('insert shrinktab select name from sysobjects delete shrinktab')
      SELECT @file = 0 
      WHILE @file < @file_max
        BEGIN
          SELECT @file = @file + 1
          SELECT @fileid = fileid FROM #logfiles WHERE id = @file
          EXEC 
          ('use ' + @DB + '; dbcc shrinkfile( ' + @fileid + ' )')
        END
      EXEC
       ( 'backup log ' + @db + ' ' + @trunc   )

      TRUNCATE TABLE #loginfo 
      INSERT #loginfo
       ( FileId,FileSize,StartOffset, FSeqNo,Status,Parity,CreateLSN )
       EXEC ( 'dbcc loginfo' )
      SELECT @last_row = @@rowcount
      SELECT @log_size = SUM(FileSize) / 1048576.00,
       @unused = SUM(CASE WHEN Status = 0 THEN FileSize
       ELSE 0
       END) / 1048576.00,
       @shrinkable = SUM(CASE WHEN id < @last_row - 1
       AND Status = 0 THEN FileSize
       ELSE 0
       END)
       / 1048576.00
       FROM #loginfo

       SELECT 'iteration' = @iteration, 'log size, MB' = @log_size, 
       'unused log, MB' = @unused, 'shrinkable log, MB' = @shrinkable, 
       'shrinkable %' = CONVERT(DECIMAL(6,2),@shrinkable
       * 100 / @log_size)
    END
 SELECT CONVERT(VARCHAR,@iteration) + ' iterations. Log shrunk from ' + 
 CONVERT(VARCHAR,@unused_at_start) + ' MB to ' + CONVERT(VARCHAR,@unused) + ' MB'
 EXEC ( 'drop table shrinktab' )

Note that this procedure wasn't written by me, but ramassed together by a good friend of mine.

Thank you,
Michael