Shrink Database

Question

Shrink Database

grc1uk

SSC Eights!

Points: 849
More actions
May 14, 2008 at 4:57 am

#187678

Hello
I have a client who is running out of disk space rapidly. Every day he goes in and runs a backup of the transaction logs using query analyzer and then right clicks on the database to shrink the database. He also has a maintenance plan in place to backup the database's daily and keep 3 days worth of backups.
I have altered this to only keep two days worth of backups and also created a new maintenance plan to backup the logs every morning so he does not have to.
My question is,.... Is there a way of automating the shrinking of the database? If so can someone point me in the correct direction. We are using SQL 2000
Thanks
Gareth

Viewing 13 posts - 1 through 12 (of 12 total)

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

Skandalis Hall of Fame Points: 3517 More actions · Answer 1

What's the size of the database? Transaction log file?

You can create a job that backs up the transaction log, say, every hour... Depending on the amount of transactions, the backup should reduce the size of the log.

But it's generally not a good idea to shrink the database itself. It will eventually grow to the same size again.

thasteve SSCrazy Points: 2707 More actions · Answer 2

you should not have to shrink at all and consider looking why it is growing like that any bad code open transactions not getting closed etc etc etc but anyways there is a task for the maintenance plan to shrink and you could set up an agent job with the dbcc shrinkfile

grc1uk SSC Eights! Points: 849 More actions · Answer 3

Hi

thanks for this

The reason that the DB's are growing is just the lack of disk space and not having the finance to buy more.

So there is a option in a maintenance plan setup to shrink the database? I must have missed this. Or would i be better using a Job to run dbcc shrinkfile???

thanks

Gareth

Skandalis Hall of Fame Points: 3517 More actions · Answer 4

Gareth, you should be fine with either one. Just read up in Books Online for syntax etc.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Make sure you rebuild all your indexes after the shrink. Shrinking a database badly fragmetns indexes.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

grc1uk SSC Eights! Points: 849 More actions · Answer 6

Thanks for this... I think im going to use the option within the right click, All Tasks, Shrink Database to set a schedule for the shrinking.

Any one come accross problems with this before??

Gareth

GSquared SSC Guru Points: 260824 More actions · Answer 7

Something I would try first, if I were you, is setting up a transaction log backup to run every 15 minutes or so. It's quite likely that this will keep the database from growing so much in the first place.

Try that for a day, see if it does what you need. If not, try shrinking it periodically. That does work, but it can really hurt the performance of the database.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Robinson SSCrazy Points: 2194 More actions · Answer 8

Like another poster said, if it is your LDF that keeps growing then maybe you just have to take more frequent transaction log backups.

You shouldn't shrink an MDF unless you delete a lot of data. If you have to shrink the MDF daily this is a Big Red Flag. It means SQL needs that space and you are fighting it. Repeated growths are expensive operations and can fragment the file on the disk. You need less data or more disk 😉

If I were you and there was absolutely 0 chance of getting more storage, I would probably look into the following:

- Archiving older data

- Purging unneeded data

- Dropping unused or lightly used indexes

Also, I would clearly document and explain the situation and tell them the consequences of not taking action. It is their choice. Let them feel the pain when they run out of disk and things grind to a halt. Unfortunately, some people won't respond until they have no other options.

Skandalis Hall of Fame Points: 3517 More actions · Answer 9

Is the cost of drive space higher than the cost of the database being offline?

Simon Facer SSCertifiable Points: 5427 More actions · Answer 10

If you rebuild Indexes (which you should) after shrinking the DB, be aware that this could potentially add a lot of data to the log.

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 11

Here's a space saving tip for your backups: enable the compression flag on the directory where you put your backups (not your mdf/ndf/ldf). It will give some CPU overhead (compression), but reduces your backupsize dramatically.

Shrinking files is a showstopper, it gives a lot of IO and CPU overhead (fileshrinking is done by one thread).

Keep asking for new hardware.

Wilfred
The best things in life are the simple things

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 12

Shrinking database files that grow everyday is a futile thing to do to try to save space and fragments the heck out of the files on the OS.

Disk space is cheap! Do your client and yourself a huge favor... tell the client to buy some more disk space.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)