Using Google Chrome?

Download my
free Chrome Extension, Power Notes Searcher, to make searching for and evaluating SAP notes, much easier.

Recent Posts

Thursday, May 01, 2014

SQL Server Shrink Transaction Log Script

Print Friendly and PDF
Below is a script that shrinks SQL Server transaction logs tested on SQL Server 2008R2.
Before running the script, you should ensure that you take a transaction log backup of your databases (which obviously means you should have already taken a full backup).  The transaction log backup will free the virtual logs from within the transaction log file.

The script simply tries to shrink the transaction log file by 25% for any databases that are not called "MASTER", "MODEL", "MSDB" or "TEMPDB".

If you wish to shrink logs by more than 25%, either change the script, or run it multiple times until it can't shrink the logs any further.

NOTE: When executing the script in SQL Management Studio, you should set the results output to "Text", so that you can see the output of the script.



USE master
GO

DECLARE @database_id    int,
        @database_name  nvarchar(128),
        @file_id        int,
        @file_name      nvarchar(128),
        @size_mb        int,
        @new_size       int;

DECLARE @dbcc_output    char(5);
DECLARE Cur_LogFiles CURSOR LOCAL FOR
  SELECT database_id,
         UPPER(DB_NAME(database_id)) database_name,
         file_id,
         name file_name,
         (size*8)/1024 size_mb
   FROM  master.sys.master_files
   WHERE type_desc = 'LOG'
     AND state_desc = 'ONLINE'
     AND UPPER(DB_NAME(database_id)) NOT IN ('MASTER','TEMPDB','MSDB','MODEL')
   ORDER BY size_mb DESC;

BEGIN
    OPEN Cur_LogFiles
    FETCH NEXT FROM Cur_LogFiles
       INTO @database_id, @database_name, @file_id, @file_name, @size_mb
    WHILE @@FETCH_STATUS = 0
    BEGIN
      -- Determine 25% of our current logfile size.
      SET @new_size = @size_mb*0.75;
      -- Set context to the database that owns the file and shrink the file with DBCC.
      PRINT 'Database: ' + @database_name + ', file: ' + @file_name + ', size: ' + CONVERT(varchar,@size_mb,25) + 'mb';
      PRINT 'Attempting to shrink file: ' + @file_name + ' by 25% to: '+ CONVERT(varchar,@new_size,25) + 'mb';

      EXEC ('USE [' + @database_name + ']; DBCC SHRINKFILE (['+@file_name+'],'+@new_size+');');

      FETCH NEXT FROM Cur_LogFiles
         INTO @database_id, @database_name, @file_id, @file_name, @size_mb
    END;

    CLOSE Cur_LogFiles
    DEALLOCATE Cur_LogFiles

END;







No comments: