Claiming back SQL Log space

2012, Nov 20    

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.recovery_model, [LdfName], ldf.size [LdfSize], 0, [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

@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
		EXEC sp_executesql @SQLUse

	--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

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

drop table #tmpDatabases