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)

as
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 <> '' 
begin
  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

end 

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

Rate

Share

Share

Rate