Shrink File Operation Not Working

Question

Shrink File Operation Not Working

Josh Spisak

SSC Enthusiast

Points: 117
More actions
May 22, 2006 at 6:17 pm

#366259

Hey guys,
Ok I have a 110 Gb database that I recenly split into 14 differnt spaces, with our program that we use to create and move the data to the spaces it leaves a bunch of unused space in the main MDF file. So generally after we do this we do a shrink file on the mdf to make it a more managable size and then detatch and move it to its final resting place. so currently the 110 GB MDF file is only using 48 GB of that, so I went to do the shink file to take it down to 50 GB and the operation is taking forever. I have canceled it a few times now, and tried to restart it but to no avail. I let it run for almost 24 hours and it still hasnt done a thing. the only thing i can think of now is use the
DBCC Shrinkfile (datafile, NOTRUNCATE) -- to move all the data to the beginning
and then use..
DBCC Shrinkfile (datafile, TRUNCATEONLY) -- to chop the end off...
I think it might be taking foerver becuase it is trying to do the two options together with just the shrink file... but im not sure. anyone got any ideas???

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Josh Spisak SSC Enthusiast Points: 117 More actions · Answer 1

sorry... its SQL server 2000 service pack 4, and there is a ton of space to do the work too. just about double the space...

Josh Spisak SSC Enthusiast Points: 117 More actions · Answer 2

Josh Spisak

SSC Enthusiast

Points: 117

May 22, 2006 at 11:21 pm

#639415

no ideas???

Josh Spisak SSC Enthusiast Points: 117 More actions · Answer 3

Josh Spisak

SSC Enthusiast

Points: 117

May 23, 2006 at 4:29 pm

#639630

still no ideas?

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 4

The most aggressive shrink is done by this command

dbcc shrinkfile(file_number) e.g. dbcc shrinkfile(2)

I'd make sure you're applied a manual checkpoint and maybe a dbcc updateusage(0) first.

the time to actually shrink may be lengthy depending upon your disk subsystem and other activities on the box.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Josh Spisak SSC Enthusiast Points: 117 More actions · Answer 5

thanks for the post, not really sure how to find the file number though, or what a manual checkpoint is either, can you expand on that a little? sorry as you can see still kind of a newbie... lol

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 6

sp_helpfile executed against the database will give filenumbers, tran log is usually 2 with primary filegroup being 1.

in qa against database use these commands

checkpoint

go

dbcc shrinkfile(fileno)

go

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Josh Spisak SSC Enthusiast Points: 117 More actions · Answer 7

thanks for the info... I have not done that yet, because what i am running now is just a dbcc shrinkdatabase (database, 20)... I saw an issue on Microsoft site that talked about a problem with shrink file when you split a database into filegroups which we have done... however that was fixed in sql 7 and we are on 2000 but i figured it was worth a shot... right now it is moving at about 7 megs an hour!!! so i figure it will be done in 190 days... all kidding aside though, i am going to be calling microsoft tomorrow morning and see if they can figure this thing out...