Technical Article

shrinking logs: how much data will be reclaimed


This script will show you how much diskspace can be reclaimed by shrinking all the logfiles of userdatabases using a DBCC SHRINKFILE(2) command.
Often the spacegain is much smaller then expected. SQL Shrinks logfiles the end of the file until it finds the active part of the logfile. (If the active part of the logfile is in the end of the file, shrinking won't reclaim any space).

usage: exec ShowSpace2BFreed

Create Proc ShowSpace2BFreed
-- By JP de Jong 
-- Stored proc that enumerates all user databases and then checks the transaction log to
-- calculate the amount of space that can be freed when an DBCC SHRINKFILE (2) statement is issued
-- To shrink a logfile use the following syntax:
-- use databasename go dbcc shrinkfile (2)

set nocount on
declare @DBName varchar(100)

Create Table #TempLOG
fileid tinyint,
filesize int,
startoffset int not null,
fseqno int,
status tinyint,
parity int,
dateorLSN varchar (25)
alter table #TempLog add constraint PK_templog Primary Key (startoffset)

Create Table #TempFreeSizes
DBName varchar(100),
FreeSpaceMB bigint

select @DBname = min(name) from master..sysdatabases where name not in ('master','tempdb','model','msdb')

while @DBName <> '' 
  insert into #TempLog exec ('use [' + @DBName + '] dbcc loginfo')

  insert into #TempFreeSizes
  select @DBName, isnull(sum(convert(bigint,filesize))/1024/1024,0) as FreeSpaceMB from #TempLog
  where startoffset > (select MAX(startoffset) from #TempLog 
  where status =2)

  truncate table #TempLog
  select @DBName = min(name) from master..sysdatabases where name not in ('master','tempdb','model','msdb') and name > @DBName


insert #TempFreeSizes (DBName, FreeSpaceMB) select '<<Total>>', sum(freespaceMB) from #TempFreeSizes
Select * from #TempFreeSizes