• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

MS SQL 2005 Srink database in chunks

Hello experts, I have a 2005 MSSQL server with a databse with a lot of free space in it.
Before any warnings about how bad it is to shrink databases, I know, and the client dimissed all my warnings.
As I'm not good in writing Queries or stored procedures, I require your help.

I know a stored procedure that will shrink the database in chunks, where the target size each time should be available free space minus 200MB. As in, if the current free space is 3 GB, that target size should be 2872 MB, next run should be 2672 etc untill the caclucation return a negative numer (i,e current free space is less than 200MB).

How would I go about creating such an SP?
0
David Sankovsky
Asked:
David Sankovsky
1 Solution
 
Eugene ZCommented:
check this one by Michael Valentine Jones
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=80355

-- Shrink_DB_File.sql
/*
This script is used to shrink a database file in
increments until it reaches a target free space limit.

Run this script in the database with the file to be shrunk.
1. Set @DBFileName to the name of database file to shrink.
2. Set @TargetFreeMB to the desired file free space in MB after shrink.
3. Set @ShrinkIncrementMB to the increment to shrink file by in MB
4. Run the script
*/

declare @DBFileName sysname
declare @TargetFreeMB int
declare @ShrinkIncrementMB int

-- Set Name of Database file to shrink
set @DBFileName = 'MyDatabaseFileName'

-- Set Desired file free space in MB after shrink
set @TargetFreeMB = 1000

-- Set Increment to shrink file by in MB
set @ShrinkIncrementMB = 50

-- Show Size, Space Used, Unused Space, and Name of all database files
select
	[FileSizeMB]	=
		convert(numeric(10,2),round(a.size/128.,2)),
	[UsedSpaceMB]	=
		convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
	[UnusedSpaceMB]	=
		convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
	[DBFileName]	= a.name
from
	sysfiles a

declare @sql varchar(8000)
declare @SizeMB int
declare @UsedMB int

-- Get current file size in MB
select @SizeMB = size/128. from sysfiles where name = @DBFileName

-- Get current space used in MB
select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

select [StartFileSize] = @SizeMB, [StartUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Loop until file at desired size
while  @SizeMB > @UsedMB+@TargetFreeMB+@ShrinkIncrementMB
	begin

	set @sql =
	'dbcc shrinkfile ( '+@DBFileName+', '+
	convert(varchar(20),@SizeMB-@ShrinkIncrementMB)+' ) '

	print 'Start ' + @sql
	print 'at '+convert(varchar(30),getdate(),121)

	exec ( @sql )

	print 'Done ' + @sql
	print 'at '+convert(varchar(30),getdate(),121)

	-- Get current file size in MB
	select @SizeMB = size/128. from sysfiles where name = @DBFileName
	
	-- Get current space used in MB
	select @UsedMB = fileproperty( @DBFileName,'SpaceUsed')/128.

	select [FileSize] = @SizeMB, [UsedSpace] = @UsedMB, [DBFileName] = @DBFileName

	end

select [EndFileSize] = @SizeMB, [EndUsedSpace] = @UsedMB, [DBFileName] = @DBFileName

-- Show Size, Space Used, Unused Space, and Name of all database files
select
	[FileSizeMB]	=
		convert(numeric(10,2),round(a.size/128.,2)),
	[UsedSpaceMB]	=
		convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
	[UnusedSpaceMB]	=
		convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
	[DBFileName]	= a.name
from
	sysfiles a

Open in new window

0
 
arnoldCommented:
Check whether the settings for db file growth are set too broadly that contribute to such a disparity.

What Is the issue that lead to the need to shrink the DB?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I have a 2005 MSSQL server with a databse with a lot of free space in it.
In data or transaction log files?

You can try with the TRUNCATE_ONLY option first to see how much space is released. If a lot then you might have it done without really changing the data pages (that's the bad thing with shrinking data files).
DBCC SHRINKFILE (file_name, TRUNCATEONLY)  
0
 
David SankovskySenior SysAdminAuthor Commented:
It worked, Thanks a lot :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now