#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 
