Shrink all SQL Server logfiles

After backing up the log files on a SQL server, the logs are truncated and you might want to reclaim the disk space held by those empty log files. This little Transact-SQL script will do just that.

DBCC SQLPERF(logspace)  
GO  
DECLARE @logsize AS INT = 256; -- MB  
DECLARE @databasename AS VARCHAR(256);  
DECLARE @filename VARCHAR(256);  
DECLARE @cmd VARCHAR(MAX);  
DECLARE curdb CURSOR FOR  
    SELECT f.name AS filename, d.name AS databasename
    FROM msdb.sys.master_files f
        INNER JOIN master.sys.sysdatabases d
        ON d.dbid = f.database_id
    WHERE type = 1 AND state = 0 AND size > @logsize AND database_id > 4    
OPEN curdb  
FETCH NEXT FROM curdb INTO @filename, @databasename  
WHILE @@FETCH_STATUS = 0  
BEGIN  
    --PRINT @filename
    SET @cmd = ('USE [' + @databasename + ']; ')
    SET @cmd = @cmd + 'DBCC SHRINKFILE ([' + @filename + '], ' + CAST(@logsize AS VARCHAR) + ');';
    EXEC (@cmd)
    FETCH NEXT FROM curdb INTO @filename, @databasename
END  
DEALLOCATE curdb  
GO  
DBCC SQLPERF(logspace)  
GO
Google
m@kli.dk @klinkby RSS feed  GitHub