Post reply

Shrink Database

  • 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

  • May 14, 2008 at 5:33 am

    #815057

    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.

  • May 14, 2008 at 5:34 am

    #815061

    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

  • May 14, 2008 at 5:59 am

    #815074

    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

  • May 14, 2008 at 6:06 am

    #815080

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

  • May 14, 2008 at 6:21 am

    #815095

    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

  • May 14, 2008 at 6:55 am

    #815124

    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

  • May 14, 2008 at 7:00 am

    #815131

    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

  • May 14, 2008 at 7:27 am

    #815158

    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.

  • May 14, 2008 at 7:49 am

    #815194

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

  • May 15, 2008 at 4:12 pm

    #816198

    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.

  • May 17, 2008 at 12:36 pm

    #816885

    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

  • May 17, 2008 at 1:07 pm

    #816890

    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)
    Intro to Tally Tables and Functions

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

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