Don't have an account yet? Sign up as a New User
Lost your password?
Shrinking Databases bit-by-bit to avoid long delays
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
We have over 13 years experience designing and building websites. We can handle websites of all sizes, from a simple web presence to a fully customised database driven website designed to handle thousands of customers a minute.
We can create you a custom website from scratch - that meets your needs as a business. And we can update your existing website if you wish.
Our design services can produce stunning website, print and logo designs that will create a recognisable brand for your business and invoke customer loyalty.
Our aim is to increase your sales and increase your customers through brand recognition and targeted marketing
Michael Jones has a neat script for shrinking databases in small chunks to
avoid having the server locked up for a long period.
