Shrinking Databases bit-by-bit to avoid long delays

SQL Server 2005
Michael Jones has a neat script for shrinking databases in small chunks to avoid having the server locked up for a long period.

declare @sql varchar(8000)
declare @name sysname
declare @sizeMB int
declare @UsedMB int
declare @FreeMB int
declare @ShrinkMB int
-- Desired free space in MB after shrink set @FreeMB = 1000
-- Increment to shrink file by in MB set @ShrinkMB = 50
-- Name of Database file to shrink set @name = 'MyDatabaseFileName'
-- Get current file size in MB select @sizeMB = size/128. from sysfiles where name = @name
-- Get current space used in MB select @UsedMB = fileproperty( @name,'SpaceUsed')/128.
select [StartFileSize] = @sizeMB, [StartUsedSpace] = @UsedMB, [File] = @name
-- Loop until file at desired size while  @sizeMB > @UsedMB+@FreeMB+@ShrinkMB   begin
  set @sql =   'dbcc shrinkfile ( ' + @name + ', '+convert(varchar(20),@sizeMB-@ShrinkMB)+' ) '
  print 'Start ' + @sql
  exec ( @sql )
  print 'Done ' + @sql
  -- Get current file size in MB   select @sizeMB = size/128. from sysfiles where name = @name     -- Get current space used in MB   select @UsedMB = fileproperty( @name,'SpaceUsed')/128.
  end
select [EndFileSize] = @sizeMB, [EndUsedSpace] = @UsedMB, [File] = @name


Share
  • Facebook
  • Google Bookmarks
  • Ask
  • LinkedIn
  • Socialogs
  • Wikio
  • Digg
  • Twitter
  • SlashDot
  • Reddit
  • MySpace
  • Del.icio.us
  • Blogter
  • BlogMemes
  • Yahoo Buzz






Shrinking Databases bit-by-bit to avoid long delays | 0 comments | Create New Account
The following comments are owned by whomever posted them. This site is not responsible for what they say.


Gold Coast Aquarium Maintenance | Gold Coast Marine Fish | Gold Coast Tropical Fish
Jewel Jones - Counselling Service - Penrith, Richmond
 
     
 Copyright © 2017 Julian Kuiters
 All trademarks and copyrights on this page are owned by their respective owners.