Shrink All Physical Files of a Database

originally posted 2013-4-19

1	originally posted 2013-4-19

Using setvar in T-SQL
Set the values of the two SQLCMD variables.

In this example, the script will only run the shrink if the ‘foo’ database has at least 100 MB to potentially free.

The script runs a shrink on each file of the database until the reduction value drops to zero.

Note: We use the “Simple” recovery mode for non-production databases, and ‘Full’ for those in production. This may affect how much the transaction log size can be reduced, specifically, the time since the last SQL backup may affect the trans-log’s shrinkability when the database is set to use ‘Full’ recovery mode.

--shrinks *ALL* files for a single database  
--that has a minimum MB of freeable space
--------------------------------------------
:setvar DbName "foo"
:setvar FreeAtLeastMB "100"

use [$(DbName)];   

declare @logname nvarchar(256)  
,@name nvarchar(256) 
,@sizeBefore int 
,@sizeAfter int  
,@reduction int  
,@totalReduction int  
,@rowcount int
,@iteration int      
,@msg nvarchar(1000)
,@originalSpaceFreeMB int
,@originalSizeMB int
,@newSpaceFreeMB int
,@newSizeMB int

set @msg = 'Working on $(DbName)';
raiserror (@msg, 0, 1) with nowait --print immediately

select 
@originalSpaceFreeMB = sum([SpaceFreeMB])
,@originalSizeMB = sum([SizeMB])
from 
(
 select 
 sf.[DbName]
 ,sf.[LogicalName]
 ,sf.[FileName]
 ,sf.[SizeMB]
 ,sf.[SpaceUsedMB]
 ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
 from
 (
  select 
  [DbName] = db_name()
  ,[LogicalName] = [Name]
  ,[Filename]
  ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))
  ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
  from sysfiles
 ) as sf
) as total

set @msg = '$(DbName) size is ' + convert(nvarchar(100),@originalSizeMB) + ' MB and has a potential of ' + convert(nvarchar(100),@originalSpaceFreeMB) + ' MB to free. Minimum freeable required is $(FreeAtLeastMB) MB.';
raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB > $(FreeAtLeastMB)) 
begin
 declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)   

 insert @tab  select [name], [physical_name], convert(int,null),convert(int,null)
 from sys.master_files 
 where [database_id] = db_id() 
 and [type] in (0,1)   

  --iterate files for the database  
 while(1=1)  
 begin   
  set @sizeBefore = 0;   
  set @sizeAfter = 0;   
  set @totalReduction = 0;  
  set @reduction = null;   
  set @iteration = 1; 
  set @name = null; 

  select top 1 @name = [name] 
  from @tab 
  where [reduction] is null;
  
  set @msg = 'Shrinking logical file ' + @name + ' of database ' + db_name();
  raiserror (@msg, 0, 1) with nowait
  
  if(@name is not null and @name <> '')   
  begin    
   --shrink the current file until the reduction is zero
   while(@reduction is null or @reduction > 0)    
   begin
    set @msg = 'shrink pass #' + convert(nvarchar(20),@iteration) + ' ...'; 
    raiserror (@msg, 0, 1) with nowait
       
    select top 1 @sizeBefore = [size] 
    from sysfiles 
    where fileid = file_id(@name);     
    
    dbcc shrinkfile (@name,20) with no_infomsgs;
    
    select top 1 @sizeAfter = [size] 
    from sysfiles 
    where fileid = file_id(@name); 
    
    set @reduction = @sizeBefore-@sizeAfter   ;  
    set @totalReduction = @totalReduction + @reduction;   
    set @iteration = @iteration + 1;
   end    
  
   update @tab 
   set [size] = ceiling((@sizeAfter * 8.) / 1024.)
   ,[reduction] =  ceiling((@totalReduction * 8.) / 1024.)
   where [name] = @name; 
  end 
  else break; 
 end   

 select 
 [Logical Name] = [name] 
 ,[File] = [filename] 
 ,[Reduction MB] = reduction 
 from @tab   
 order by [name];  
 
 select 
 @newSpaceFreeMB = sum([SpaceFreeMB])
 ,@newSizeMB = sum([SizeMB])
 from 
 (
  select 
  sf.[DbName]
  ,sf.[LogicalName]
  ,sf.[FileName]
  ,sf.[SizeMB]
  ,sf.[SpaceUsedMB]
  ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
  from
  (
   select 
   [DbName] = db_name()
   ,[LogicalName] = [Name]
   ,[Filename]
   ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))
   ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
   from sysfiles
  ) as sf
 ) as total
 
 select 
 [OriginalSizeMB] =  @originalSizeMB
 ,[OriginalSpaceFreeMB] = @originalSpaceFreeMB
 ,[NewSizeMB] = @newSizeMB
 ,[NewSpaceFreeMB] = @newSpaceFreeMB
 ,[ReductionMB] = @originalSizeMB-@newSizeMB
end
else
begin
 set @msg = 'Skipping shrink b/c $(DbName) does not have the requisite $(FreeAtLeastMB) MB free.';
 raiserror (@msg, 0, 1) with nowait
end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

--shrinks *ALL* files for a single database

--that has a minimum MB of freeable space

--------------------------------------------

:setvar DbName "foo"

:setvar FreeAtLeastMB "100"

use [$(DbName)];

declare @logname nvarchar(256)

,@name nvarchar(256)

,@sizeBefore int

,@sizeAfter int

,@reduction int

,@totalReduction int

,@rowcount int

,@iteration int

,@msg nvarchar(1000)

,@originalSpaceFreeMB int

,@originalSizeMB int

,@newSpaceFreeMB int

,@newSizeMB int

set @msg = 'Working on $(DbName)';

raiserror (@msg, 0, 1) with nowait --print immediately

select

@originalSpaceFreeMB = sum([SpaceFreeMB])

,@originalSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

set @msg = '$(DbName) size is ' + convert(nvarchar(100),@originalSizeMB) + ' MB and has a potential of ' + convert(nvarchar(100),@originalSpaceFreeMB) + ' MB to free. Minimum freeable required is $(FreeAtLeastMB) MB.';

raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB > $(FreeAtLeastMB))

begin

declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)

insert @tab select [name], [physical_name], convert(int,null),convert(int,null)

from sys.master_files

where [database_id] = db_id()

and [type] in (0,1)

--iterate files for the database

while(1=1)

begin

set @sizeBefore = 0;

set @sizeAfter = 0;

set @totalReduction = 0;

set @reduction = null;

set @iteration = 1;

set @name = null;

select top 1 @name = [name]

from @tab

where [reduction] is null;

set @msg = 'Shrinking logical file ' + @name + ' of database ' + db_name();

raiserror (@msg, 0, 1) with nowait

if(@name is not null and @name <> '')

begin

--shrink the current file until the reduction is zero

while(@reduction is null or @reduction > 0)

begin

set @msg = 'shrink pass #' + convert(nvarchar(20),@iteration) + ' ...';

raiserror (@msg, 0, 1) with nowait

select top 1 @sizeBefore = [size]

from sysfiles

where fileid = file_id(@name);

dbcc shrinkfile (@name,20) with no_infomsgs;

select top 1 @sizeAfter = [size]

from sysfiles

where fileid = file_id(@name);

set @reduction = @sizeBefore-@sizeAfter ;

set @totalReduction = @totalReduction + @reduction;

set @iteration = @iteration + 1;

end

update @tab

set [size] = ceiling((@sizeAfter * 8.) / 1024.)

,[reduction] = ceiling((@totalReduction * 8.) / 1024.)

where [name] = @name;

end

else break;

end

select

[Logical Name] = [name]

,[File] = [filename]

,[Reduction MB] = reduction

from @tab

order by [name];

select

@newSpaceFreeMB = sum([SpaceFreeMB])

,@newSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],'SpaceUsed')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

select

[OriginalSizeMB] = @originalSizeMB

,[OriginalSpaceFreeMB] = @originalSpaceFreeMB

,[NewSizeMB] = @newSizeMB

,[NewSpaceFreeMB] = @newSpaceFreeMB

,[ReductionMB] = @originalSizeMB-@newSizeMB

end

else

begin

set @msg = 'Skipping shrink b/c $(DbName) does not have the requisite $(FreeAtLeastMB) MB free.';

raiserror (@msg, 0, 1) with nowait

end

Here’s the same thing as a stored procedure.

use [master];
create procedure spShrinkDatabase(@dbname nvarchar(255), @freeAtLeastMB int)
as
begin
	--------------------------------------------
	--shrinks *ALL* files for a single database  
	--that has a minimum MB of freeable space
	--------------------------------------------
	if(@dbname is null or len(@dbname)=0) 
	begin
		raiserror ('No database name specified.', 0, 1) with nowait --print immediately
		return;
	end

	if(@freeAtLeastMB is null or @freeAtLeastMB <= 0) set @freeAtLeastMB = 1;
	declare @freeAtLeastMBStr nvarchar(20);
        set = @freeAtLeastMBStr = convert(nvarchar(20),@freeAtLeastMB);

	declare @sql nvarchar(max);
	set @sql = 'use [' +  @dbname + '];   
	declare @logname nvarchar(256)  
	,@name nvarchar(256) 
	,@sizeBefore int 
	,@sizeAfter int  
	,@reduction int  
	,@totalReduction int  
	,@rowcount int
	,@iteration int      
	,@msg nvarchar(1000)
	,@originalSpaceFreeMB int
	,@originalSizeMB int
	,@newSpaceFreeMB int
	,@newSizeMB int

	set @msg = ''Working on ' +  @dbname + ''';
	raiserror (@msg, 0, 1) with nowait --print immediately

	select 
	@originalSpaceFreeMB = sum([SpaceFreeMB])
	,@originalSizeMB = sum([SizeMB])
	from 
	(
	 select 
	 sf.[DbName]
	 ,sf.[LogicalName]
	 ,sf.[FileName]
	 ,sf.[SizeMB]
	 ,sf.[SpaceUsedMB]
	 ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
	 from
	 (
	  select 
	  [DbName] = db_name()
	  ,[LogicalName] = [Name]
	  ,[Filename]
	  ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))
	  ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
	  from sysfiles
	 ) as sf
	) as total

	set @msg = ''' +  @dbname + ' size is '' + convert(nvarchar(100),@originalSizeMB) + '' MB and has a potential of '' + convert(nvarchar(100),@originalSpaceFreeMB) + '' MB to free. Minimum freeable required is ' +  @freeAtLeastMBStr + ' MB.'';
	raiserror (@msg, 0, 1) with nowait

	if(@originalSpaceFreeMB >= ' + @freeAtLeastMBStr + ') 
	begin
	 declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)   

	 insert @tab  select [name], [physical_name], convert(int,null),convert(int,null)
	 from sys.master_files 
	 where [database_id] = db_id() 
	 and [type] in (0,1)   

	  --iterate files for the database  
	 while(1=1)  
	 begin   
	  set @sizeBefore = 0;   
	  set @sizeAfter = 0;   
	  set @totalReduction = 0;  
	  set @reduction = null;   
	  set @iteration = 1; 
	  set @name = null; 

	  select top 1 @name = [name] 
	  from @tab 
	  where [reduction] is null;
  
	  set @msg = ''Shrinking logical file '' + @name + '' of database '' + db_name();
	  raiserror (@msg, 0, 1) with nowait
  
	  if(@name is not null and @name <> '''')   
	  begin    
	   --shrink the current file until the reduction is zero
	   while(@reduction is null or @reduction > 0)    
	   begin
		set @msg = ''shrink pass #'' + convert(nvarchar(20),@iteration) + '' ...''; 
		raiserror (@msg, 0, 1) with nowait
       
		select top 1 @sizeBefore = [size] 
		from sysfiles 
		where fileid = file_id(@name);     
    
		dbcc shrinkfile (@name,20) with no_infomsgs;
    
		select top 1 @sizeAfter = [size] 
		from sysfiles 
		where fileid = file_id(@name); 
    
		set @reduction = @sizeBefore-@sizeAfter   ;  
		set @totalReduction = @totalReduction + @reduction;   
		set @iteration = @iteration + 1;
	   end    
  
	   update @tab 
	   set [size] = ceiling((@sizeAfter * 8.) / 1024.)
	   ,[reduction] =  ceiling((@totalReduction * 8.) / 1024.)
	   where [name] = @name; 
	  end 
	  else break; 
	 end   

	 select 
	 [Logical Name] = [name] 
	 ,[File] = [filename] 
	 ,[Reduction MB] = reduction 
	 from @tab   
	 order by [name];  
 
	 select 
	 @newSpaceFreeMB = sum([SpaceFreeMB])
	 ,@newSizeMB = sum([SizeMB])
	 from 
	 (
	  select 
	  sf.[DbName]
	  ,sf.[LogicalName]
	  ,sf.[FileName]
	  ,sf.[SizeMB]
	  ,sf.[SpaceUsedMB]
	  ,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]
	  from
	  (
	   select 
	   [DbName] = db_name()
	   ,[LogicalName] = [Name]
	   ,[Filename]
	   ,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))
	   ,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))
	   from sysfiles
	  ) as sf
	 ) as total
 
	 select 
	 [OriginalSizeMB] =  @originalSizeMB
	 ,[OriginalSpaceFreeMB] = @originalSpaceFreeMB
	 ,[NewSizeMB] = @newSizeMB
	 ,[NewSpaceFreeMB] = @newSpaceFreeMB
	 ,[ReductionMB] = @originalSizeMB-@newSizeMB
	end
	else
	begin
	 set @msg = ''Skipping shrink b/c ' +  @dbname + ' does not have the requisite ' + @freeAtLeastMBStr + ' MB free.'';
	 raiserror (@msg, 0, 1) with nowait
	end';

	--select @sql
	exec(@sql);
end

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

use [master];

create procedure spShrinkDatabase(@dbname nvarchar(255), @freeAtLeastMB int)

begin

--------------------------------------------

--shrinks *ALL* files for a single database

--that has a minimum MB of freeable space

--------------------------------------------

if(@dbname is null or len(@dbname)=0)

begin

raiserror ('No database name specified.', 0, 1) with nowait --print immediately

return;

end

if(@freeAtLeastMB is null or @freeAtLeastMB <= 0) set @freeAtLeastMB = 1;

declare @freeAtLeastMBStr nvarchar(20);

set = @freeAtLeastMBStr = convert(nvarchar(20),@freeAtLeastMB);

declare @sql nvarchar(max);

set @sql = 'use [' + @dbname + '];

declare @logname nvarchar(256)

,@name nvarchar(256)

,@sizeBefore int

,@sizeAfter int

,@reduction int

,@totalReduction int

,@rowcount int

,@iteration int

,@msg nvarchar(1000)

,@originalSpaceFreeMB int

,@originalSizeMB int

,@newSpaceFreeMB int

,@newSizeMB int

set @msg = ''Working on ' + @dbname + ''';

raiserror (@msg, 0, 1) with nowait --print immediately

select

@originalSpaceFreeMB = sum([SpaceFreeMB])

,@originalSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

set @msg = ''' + @dbname + ' size is '' + convert(nvarchar(100),@originalSizeMB) + '' MB and has a potential of '' + convert(nvarchar(100),@originalSpaceFreeMB) + '' MB to free. Minimum freeable required is ' + @freeAtLeastMBStr + ' MB.'';

raiserror (@msg, 0, 1) with nowait

if(@originalSpaceFreeMB >= ' + @freeAtLeastMBStr + ')

begin

declare @tab table ([name] nvarchar(256), [filename] nvarchar(256),[size] int, [reduction] int)

insert @tab select [name], [physical_name], convert(int,null),convert(int,null)

from sys.master_files

where [database_id] = db_id()

and [type] in (0,1)

--iterate files for the database

while(1=1)

begin

set @sizeBefore = 0;

set @sizeAfter = 0;

set @totalReduction = 0;

set @reduction = null;

set @iteration = 1;

set @name = null;

select top 1 @name = [name]

from @tab

where [reduction] is null;

set @msg = ''Shrinking logical file '' + @name + '' of database '' + db_name();

raiserror (@msg, 0, 1) with nowait

if(@name is not null and @name <> '''')

begin

--shrink the current file until the reduction is zero

while(@reduction is null or @reduction > 0)

begin

set @msg = ''shrink pass #'' + convert(nvarchar(20),@iteration) + '' ...'';

raiserror (@msg, 0, 1) with nowait

select top 1 @sizeBefore = [size]

from sysfiles

where fileid = file_id(@name);

dbcc shrinkfile (@name,20) with no_infomsgs;

select top 1 @sizeAfter = [size]

from sysfiles

where fileid = file_id(@name);

set @reduction = @sizeBefore-@sizeAfter ;

set @totalReduction = @totalReduction + @reduction;

set @iteration = @iteration + 1;

end

update @tab

set [size] = ceiling((@sizeAfter * 8.) / 1024.)

,[reduction] = ceiling((@totalReduction * 8.) / 1024.)

where [name] = @name;

end

else break;

end

select

[Logical Name] = [name]

,[File] = [filename]

,[Reduction MB] = reduction

from @tab

order by [name];

select

@newSpaceFreeMB = sum([SpaceFreeMB])

,@newSizeMB = sum([SizeMB])

from

(

select

sf.[DbName]

,sf.[LogicalName]

,sf.[FileName]

,sf.[SizeMB]

,sf.[SpaceUsedMB]

,[SpaceFreeMB] = sf.[SizeMB] - sf.[SpaceUsedMB]

from

(

select

[DbName] = db_name()

,[LogicalName] = [Name]

,[Filename]

,[SpaceUsedMB] = convert(int,round(fileproperty([Name],''SpaceUsed'')/128.000,0))

,[SizeMB] = convert(int,round(([Size]*1.000)/128.000,0))

from sysfiles

) as sf

) as total

select

[OriginalSizeMB] = @originalSizeMB

,[OriginalSpaceFreeMB] = @originalSpaceFreeMB

,[NewSizeMB] = @newSizeMB

,[NewSpaceFreeMB] = @newSpaceFreeMB

,[ReductionMB] = @originalSizeMB-@newSizeMB

end

else

begin

set @msg = ''Skipping shrink b/c ' + @dbname + ' does not have the requisite ' + @freeAtLeastMBStr + ' MB free.'';

raiserror (@msg, 0, 1) with nowait

end';

--select @sql

exec(@sql);

end