#sp_shrinkdata

,

Unlike shrinking transaction log, shrinking a data file seems to be tough. A lot of people complained about the "shrink database" option in EM not working, or DBCC SHRINKDATABASE not working, or DBCC SHRINKFILE not working. I have found that a data file will eventually shrink given enough try, time and patience. I've put everything together in a temporary stored procedure. I don't want to use a permanent stored proc due to the need to access sysfiles table in each database. It has been tested on both SQL 7 and 2000.

Note: the accuracy of size depends on how update the statistics on data usage.   

if exists ( select [name] from tempdb.dbo.sysobjects (nolock) where [name] like '#sp_shrinkdata%' and type = 'P' )
    drop proc #sp_shrinkdata
go

create proc #sp_shrinkdata 
	@updateusage bit = 0,
	@freed_space int = null,		--	is the portion of free space (MB) to reclaim, eg. 90% of 1 gb is 900 mb
	@Msg_rtn_intv int = null		--	is the size of reclaimed space (MB) displayed between each message echo
as
/*
**		Author: Richard Ding
**		Created on: 7/4/2002
**		Modified on: 1/9/2003
**	  Purpose: Shrinks data file (not log).
**		Features:
**			1. Can shrink data size down to a specified value (optional). If desired size not provided, it 
**				 will shrink all the way down until there's 200 MB free space left;
**			2. Can report time, data size and cycle number after each run. If echo interval not supplied, 
**				 it will report at every 20 MB interval.
**		Requirements:
**		1. Must be in the current database.
**		2. Must first compile the stored procedure.
*/
declare @initial_size int, 
				@end_size int, 
				@message varchar(150), 
				@count int, 
				@data_used int, 
				@cmd varchar(500), 
				@datafile sysname, 
				@dbname sysname,
				@data_free_start int

set @dbname = db_name()
set @count = 1
set @datafile = (select rtrim(name) from sysfiles (nolock) where fileid = 1)		-- Trims away trailing blanks or will NOT work!!
set @initial_size = (select sum(size) from sysfiles (nolock) where (status & 64 = 0)) * 8192.0 / 1048576.0
set @data_used = (select sum(convert(dec(15),reserved)) from sysindexes (nolock) where indid in (0, 1, 255)) * 8192.0 / 1048576.0
if @Msg_rtn_intv is null 
	set @Msg_rtn_intv = 20		--	default to 20 mb at each interval
if @freed_space is null
	set @freed_space = @initial_size - @data_used - 200		--	default to 200 MB free space left after shrinking

set @end_size = @initial_size - @freed_space	
if @updateusage = 1
exec ('dbcc updateusage (0) WITH NO_INFOMSGS')
print '...... DBCC UPDATEUSAGE complete.'
print''
print '***  Shrinking data file for ' + db_name() + '  ***'
print ''
set @data_free_start = (@initial_size-@data_used)
print 'Total size of data: ' + convert(char(5),@initial_size) 
			+ ' MB                Data used: ' + convert(char(5),@data_used) 
			+ ' MB                Data free: ' + convert(char(5),@data_free_start) + ' MB' 
print ''

while @initial_size > @end_size
begin
set @initial_size = @initial_size - @Msg_rtn_intv
set @cmd = N' dbcc shrinkfile ('+@datafile+', '+convert(varchar(5), @initial_size)+') WITH NO_INFOMSGS'
exec (@cmd)
set @message = convert(varchar(30), getdate(), 20) + '       - at the ' + convert (varchar(6), @count) + 'th run. ' + 
	'        - Free space left: ' + convert(char(5),(@initial_size - @data_used)) + ' MB'  
print @message

set @count = @count + 1
end
print ''
print 'Total reclaimed space: ' + cast((@data_free_start - (@initial_size - @data_used)) as varchar(10)) + ' MB'
print ''
print '***  Shrinking data file for ' + db_name() + ' is complete.  ***'

go

--	#sp_shrinkdata 
--	#sp_shrinkdata @updateusage = 1, @freed_space = 100 ,  @Msg_rtn_intv = 10

Rate

Share

Share

Rate