Solved

MS SQL 2005 Srink database in chunks

Posted on 2016-09-11
4
58 Views
Last Modified: 2016-09-15
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
Comment
Question by:David Sankovsky
4 Comments
 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
ID: 41793269
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
 
LVL 77

Expert Comment

by:arnold
ID: 41793330
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
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 41793828
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
 
LVL 7

Author Closing Comment

by:David Sankovsky
ID: 41799770
It worked, Thanks a lot :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question