I have made a script which shrinking all log files on a given database server.

It was needed because we had a (development) server that was running low on space with regular intervals and the main culprit was growing/large log files from the ever changing amount of databases on the server.
Now, as always – remember there are consequences for doing this; it will mess with your backup plan if relying on the transaction logs, so use it after full backups and …. Well, use your common sense :)
The script checks for offline databases and ignore those.

DECLARE @dbfile VARCHAR(255)
DECLARE @isOnline BIT
DECLARE @isfull BIT

DECLARE db_cursor CURSOR FOR
SELECT
name, state FROM sys.databases AS D WHERE owner_sid <> 0x01

OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @dbfile, @isOnline

WHILE @@FETCH_STATUS = 0 BEGIN    
    PRINT
('Working on: ' + @dbfile + '; Online: ' + LTRIM(STR(@IsOnline)))
    
IF @isOnline = 0BEGIN
        SELECT
@isfull =
                
CASE                    
                    
WHEN recovery_model = 1 THEN 1
                    
ELSE  0
                
END
        FROM
sys.databases AS D WHERE name = @dbfile
        
        
IF @isfull = 1 BEGIN
            PRINT
('Setting DB: ' + @dbfile + ' to simple')
            
EXEC('ALTER DATABASE [' + @dbfile +'] SET RECOVERY SIMPLE')
        
END ELSE BEGIN
            PRINT
('DB: ' + @dbfile + ' was already simple, skip setting to simple')
        
END          
        
        EXEC
(
        
'USE [' + @dbfile + ']            
        DECLARE file_cursor CURSOR FOR
        SELECT name FROM sys.database_files AS DF WHERE type_desc = ''log''
        DECLARE @file VARCHAR(255)        
        OPEN file_cursor
        FETCH NEXT FROM file_cursor INTO @file
        WHILE @@FETCH_STATUS = 0 BEGIN
            PRINT(''Shrinking file: '' + @file)
            DBCC SHRINKFILE(@file)
            FETCH NEXT FROM file_cursor INTO @file
        END
        
        CLOSE file_cursor
        DEALLOCATE file_cursor
        '
)
        
        
IF @isfull = 1 BEGIN    
            PRINT
('Setting DB: ' + @dbfile + ' to full')
            
EXEC('ALTER DATABASE [' + @dbfile +'] SET RECOVERY FULL')
        
END ELSE BEGIN
            PRINT
('DB: ' + @dbfile + ' was simple, skip setting to full')
        
END        
    END ELSE BEGIN
        PRINT
('Database is offline, skip')
    
END
    PRINT
(CHAR(13) + CHAR(10))
    
FETCH NEXT FROM db_cursor INTO @dbfile, @isOnline
END
CLOSE
db_cursor
DEALLOCATE db_cursor  

			

One Response to “Script for shrinking transaction logs on server”

  1. Helpful tips. I also suffered with big log file issue as I was not performing transaction log backups. There are many reasons of big log file like Uncommitted transactions, Extremely large transactions etc. You should backup your transaction logs on a regular interval to prevent your log file to grow.

Leave a Reply

(required)

(required)

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>