Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 68
  • Last Modified:

batch job to shrink data file till its a certain size

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
richa1960
Asked:
richa1960
  • 4
  • 2
1 Solution
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
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
 
richa1960Author Commented:
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
 
David ToddSenior DBACommented:
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
richa1960Author Commented:
I'll be testing the script this weekend on a dev system we have setup.
0
 
richa1960Author Commented:
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
 
David ToddSenior DBACommented:
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
 
richa1960Author Commented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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