Skip to main content

A very helpful SQL Server script to periodically free up disk space in shared development environments (DEV), and especially local VMs.

-- article:
-- github:

declare @commandQueue table (ID int identity(1, 1), SqlStatement varchar(1000) not null);

insert into @commandQueue (SqlStatement)
    select 'BACKUP LOG [' + name + '] WITH TRUNCATE_ONLY'
    from sys.databases
    where name not in ('master', 'model', 'msdb', 'tempdb');

declare @id int;
select @id = min(ID) from @commandQueue;

while @id is not null
        declare @sqlStatement varchar(1000);
        select @sqlStatement = SqlStatement from @commandQueue where ID = @id;

        print 'Executing ''' + @sqlStatement + '''...';
        exec (@sqlStatement);

        delete from @commandQueue where ID = @id;
        select @id = min(ID) from @commandQueue;