Post reply

Help Requested to Shrink Table Size

  • December 28, 2006 at 8:49 am

    #75108

    Hi

     

    I have a database with a big data file [about 100 GB] which is heavily used in business hour. I executed sp_spaceused for some user tables space usage monitoring there and fund that there is good amount of space reserved and unused for them [The space populated in the unused column in the SP output]. I want to reduce this space reserve so that I can battle with continuous space crunch.

    Could anyone kindly tell how to achieve that?

    Database is set to grow in MB only.

     

    The steps I thought:

    1. To defragment the server [If ok from SYSADMIN]
    2. Shrinking DB

    But will it really help?

     

    Thanks in advance for your help.

     

    Regards

    Utsab Chattopadhyay


    CDBA

  • December 28, 2006 at 9:24 am

    #680288

    you really don't want to go down this path unless you want to degrade your database performance.

    • Minimising database space can be better achieved by making sure all your tables have a clustered index so that you can remove leaf level fragmentation
    • Don't have any silly fill factors set unless you know they are effective/useful , an 80% fill factor across your database will add 20% min to your database size
    • Rebuild your indexes to optimise the data within the database. run dbcc updateusage to make sure sp_spaceused is accurate (ish )

    I really don't know why everyone is so hung up on space these days, disks are cheap and there's little you can do to shrink the physical data in your database, true you might be concerned about a 100Gb mdf file, if it only contains 2Gb of data, ok it's a little on the large size, but if it contains, say, 90Gb of data what's the problem? The backups will only be the size of the data ( or smaller if you use litespeed ) the actual size of the mdf only affects the size of the restored database not the size of the backup.

    Find one of the inside sql books to read up about what you're looking at - I think you may have the wrong idea about it all!

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

  • December 28, 2006 at 10:40 am

    #680315

    To handle individual tables, you can rebuild the clustered index, which will remove the data around and reassemble it with less fragmentation. However, you want some fragmentation in the tables. When you insert/update data, sometimes you need space. This space is determined by the "fillfactor" when you build the clustered index.

    Read thi: http://www.sqlservercentral.com/columnists/jdoherty/2760.asp

Viewing 3 posts - 1 through 3 (of 3 total)

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