Solved

MS SQL 2005 Srink database in chunks

Posted on 2016-09-11
4
76 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 43

Accepted Solution

by:
Eugene Z 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 78

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 50

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

734 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