Post reply

Why SHRINKFILE is a very bad thing, and what to do about it.

  • April 1, 2012 at 12:28 am

    #1467380

    Thanks for advice on 'new thread' thingie 🙂

    As for db, we really need to find a way to create some free space on drive. Now we have around 50-70 GBs (1%) to mess around with, on disk containing this datafile. Database is in simple recovery model. In other words, I need to find a way to shrink the 1,5TB file without messing up indexes.

  • April 1, 2012 at 4:04 am

    #1467385

    is there other data on this drive other than the database then?

    Is the log file on the same drive and if so what size is it? a shrink of the log is less destructive.

    some ideas, but maybe you just need another drive to support and maintain this database. Is it expected to grow in the future?

    try a shrinkfile with the truncateonly option, that just chops off the unused end of the file and returns it to the OS without moving data about. However in most cases there is data right at the end of the file and you get nothing back.

    Look at the growth factor of the file, if its a percentage the file might actually have grown a lot more than it needed to to accommodate actual space needs. Adjust it and try again.

    Dont just rebuild everything in one go, rebuild in chunks. Only rebuild indexes that need rebuilding. There are loads of scripts to achieve that including a good basic one in BOL.

    If you shrink a data file the data will be fragmented, there is NO way round that

    ---------------------------------------------------------------------

  • April 2, 2012 at 9:12 am

    #1467679

    If you have enough space on another drive create a new database, copy/export the data to it from the existing db and arrange it in a more logical manner. If you don't have enough space you're probably a bit stuck. A general rule of thumb I use is not to let dbs grow more than 1Tb.

  • April 2, 2012 at 9:23 am

    #1467686

    I'd be looking to add either files or filegroups to the database first and moving data accordingly.

    ---------------------------------------------------------------------

  • April 2, 2012 at 9:25 am

    #1467687

    As I said, arrange it in a more logical manner.

Viewing 5 posts - 106 through 109 (of 109 total)

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