Claiming back SQL Log space

I recently had the problem where one of our staging database servers had run out of disk space It contained about 40 databases, each of which was set to Full Recovery mode.

The maintenance jobs had been turned off which meant the log files of each of the databases had grown (the average was a 6gb log) I needed a way to claim back this disk space and without too much effort, and with no regard to what was in the logs. Here’s what i came up with.

It iterates through all databases on an instance of SQL,

CREATE TABLE #tmpDatabases
(
   RowID int IDENTITY(1, 1), 
   [dbId] int,
   dbName VARCHAR(255), 
   recoveryModel int,
   ldfName varchar(255), 
   oldLdfSize int, 
   newLdfSize int,
   mdfName varchar(255), 
   mdfSize int
)

INSERT INTO #tmpDatabases 
select d.database_id, d.name, d.recovery_model, ldf.name [LdfName], ldf.size [LdfSize], 0, mdf.name [MdfName], mdf.size [MdfSize]
from sys.databases d
inner join sys.sysaltfiles ldf on d.database_id = ldf.dbid and ldf.filename like '%ldf'
inner join sys.sysaltfiles mdf on d.database_id = mdf.dbid and mdf.filename like '%mdf'
where d.database_id>4 --only user databases

DECLARE 
@RecoveryModel int,
@DbName varchar(255),
@DbId int,
@LdfName varchar(255),
@SQLUse nvarchar(1024),
@NumberRecords INT,
@RowCount INT

-- Get the number of records in the temporary table
SET @NumberRecords = @@ROWCOUNT
SET @RowCount = 1

WHILE @RowCount  3
	BEGIN
		SET @SQLUse = 'ALTER DATABASE ' + @DbName + ' SET RECOVERY SIMPLE WITH NO_WAIT; '
		EXEC sp_executesql @SQLUse
	END

	--Shrink log file
	SET @SQLUse = 'USE ' + @DbName + '; SELECT DB_NAME() [Current Db]; CHECKPOINT; DBCC SHRINKFILE(' + @LdfName + ', 50); '
	EXEC sp_executesql @SQLUse

	--get new log file size
	update #tmpDatabases
	set newLdfSize = ldf.size
	from #tmpDatabases
	inner join sys.sysaltfiles ldf on #tmpDatabases.[dbid] = ldf.dbid and ldf.filename like '%ldf'
	where #tmpDatabases.[dbId] = @DbId

	--output progress
	select * from #tmpDatabases where #tmpDatabases.[dbId] = @DbId

	SET @RowCount = @RowCount + 1
END

select *, oldLdfSize-newLdfSize [LdfDiff] from #tmpDatabases order by oldLdfSize-newLdfSize desc

drop table #tmpDatabases