Shrinking Databases

Question

Shrinking Databases

Graeme100

SSCrazy Eights

Points: 8054
More actions
February 22, 2005 at 1:13 am

#162661

Hi Guys,
I have a backup job that includes shrinking the databases...pretty standard stuff.
However, every now and then, the shrink step fails because it has been chosen as the victim in a deadlock.
Does any one have any ideas what sort of deadlock causes a shrink to fail ?
Cheers..Graeme

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

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

David.Poole SSC Guru Points: 76000 More actions · Answer 1

I am pretty sure that shrinking the database requires an exclusive lock on the database so if anyone access the database the shrink will fail.

LinkedIn Profile

Greg Milner SSCrazy Points: 2425 More actions · Answer 2

You might try Yul Wasserman's uspKillAllProcesses script first, right before you run the shrink.

April 5, 2002

Terminate All User Processes Script

http://www.databasejournal.com/scripts/article.php/1491381

Also see his other scripts:

April 12, 2002

Three Administrative Scripts

http://www.databasejournal.com/scripts/article.php/1491371

The one that sets the database to single user mode might be handy.

G. Milner

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 3

Hey,

Thanks for that.

I shall certainly have a look at those.

Thanks for your input.

Graeme

fcallawa Mr or Mrs. 500 Points: 531 More actions · Answer 4

I do not believe that one needs exclusive access to a database as I have shrunk databases on the fly in the past.

I have encountered this problem though whenever a transaction log backup gets kicked off before or during a shrink DB attempt.

Vik Nat Hall of Fame Points: 3930 More actions · Answer 5

Do this during off-peak times or times when activity is minimal on the DB server.

hoo-t SSChampion Points: 12713 More actions · Answer 6

From a different angle... I'm not sure I agree that shrinking the database in the scheduled backup job is standard stuff. Are you backing up weekly? daily? more often? If you are shrinking your database with every backup, that would imply that the database is growing between every backup. If your database is OLTP, then you are forcing the database to expand when a user issues an update or insert, causing the user to wait for the expand to finish before his/her transaction can complete. In my opinion, you would be much, much better off to get rid of the shrink step in your backup job, and allow the database to settle in at its optimum size, which will probably have a significant positive effect on performance.

Steve