Solved

batch job to shrink data file till its  a certain size

Posted on 2014-04-23
7
54 Views
Last Modified: 2015-07-15
I have a server that has a large database we'd like to shrink and get some of the space
back. I know I can use 'DBCC shrinkfile (DBNAME, SIZE) ' we'd like to do it in chunks because of the size of the file and its a production database. is there a way to built it into a batch job that does 10GB chunks till its the desired size we'd like.
0
Comment
Question by:richa1960
  • 4
  • 2
7 Comments
 
LVL 30

Accepted Solution

by:
Rich Weissler earned 400 total points
ID: 40019800
I assume you already know all the reasons why you shouldn't shrink database files.  Many folks, far smarter than I, have invested a huge number of bits explaining why it's a very bad idea.

I also assume you're really working with the datafile, not the transaction log.

I don't believe cutting off chunks of your datafile at a time will work quite the way you hope.  If you HAVE to perform the shrink operation, do it just once during a maintenance window, and be sure to leave enough time to fix the index fragmentation shrinking causes.  (It also means, you'll need the NEW size of the database to be large enough to complete the reindex/reorganization operations.)
0
 

Author Comment

by:richa1960
ID: 40020423
Yes we're only dealing with the data file, the problem with doing it in a maint window is that the file is so large it wouldn't complete in time for customer to continue to access their data. so we'd like to do it in batches over a longer period to minimize the impact. we've accounted for the re-index its the primary process we're dealing with.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40021292
Hi,

This should do it.

Do set the fileid correctly, do set the shrink limit, do select the correct database.

HTH
  David

declare @fileid int
set @fileid = 1

select size * 8 * 1024
from dbo.sysfiles
where
	fileid = @fileid
;

declare @size bigint

while 1 = 1 begin
	-- size is 8k pages
	-- @size is bytes
	select @size = size * 8 * 1024
	from dbo.sysfiles
	where
		fileid = @fileid
	;

        -- 100 is limit to shrink to in GB
	if @size / 1024 / 1024 / 1024.0 < 100
		break
	;

	-- @targetsize is size less 10GB expressed in MB
	declare @targetsize int
	set @targetsize = ( @size / 1024.0 / 1024.0 / 1024.0 - 10 ) * 1024

	dbcc shrinkfile( @fileid, @targetsize )

end

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:richa1960
ID: 40054792
I'll be testing the script this weekend on a dev system we have setup.
0
 

Author Comment

by:richa1960
ID: 40067360
Receiving the following error:

Msg 8115, Level 16, State 2, Line 5
Arithmetic overflow error converting expression to data type int.
Msg 8115, Level 16, State 2, Line 16
Arithmetic overflow error converting expression to data type int.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40068689
Hi,

I've added some explicit casts to bigint that should take care of the overflows.

I've added checks to make sure that each subsequent iteration through the loop isn't taking too long.

HTH
  David

declare @fileid int
set @fileid = 1

select size * 8 * cast( 1024 as bigint )
from dbo.sysfiles
where
	fileid = @fileid
;

declare @size bigint
declare @LoopStart datetime
declare @CurrentDuration  int
declare @PreviousDuration int

while 1 = 1 begin
	select @LoopStart = getdate()

	-- if each iteration is getting longer, then back off
	if @CurrentDuration > 2 * @PreviousDuration begin
		print 'Starting to take too long. Breaking ...'
		break
	end
	;

	-- size is 8k pages
	-- @size is bytes
	select @size = size * 8 * cast( 1024 as bigint )
	from dbo.sysfiles
	where
		fileid = @fileid
	;

        -- 100 is limit to shrink to in GB
	if @size / 1024 / 1024 / 1024.0 < 100 begin
		print 'At desired limit. Breaking ...'
		break
	end
	;

	-- @targetsize is size less 10GB expressed in MB
	declare @targetsize int
	set @targetsize = ( @size / 1024.0 / 1024.0 / 1024.0 - 10 ) * 1024

	print 'shrinking to ' + convert( varchar( max ), @targetsize )
	dbcc shrinkfile( @fileid, @targetsize )

	select @PreviousDuration = @CurrentDuration
	select @CurrentDuration = datediff( ms, @LoopStart, getdate())
end

Open in new window

0
 

Author Comment

by:richa1960
ID: 40117578
Sorry, I've been out with a personal matter to deal with, the script will be run in production this weekend, thanks.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

766 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