Post reply

I want to reclaim the space, but I dont want using Shrink Database

  • July 25, 2012 at 12:25 pm

    #1517568

    shrinking the data files causes a lot of index fragmentation and a lot of pressure on disk subsystem because it moves data pages from the end of data file to beginning of it(unless using the truncateonly option) it also causes physical fragmentation on the disk in case of the data files authogrowth.So unless in rare conditions you should avoid it whether auhomatically or manually.

    Pooyan

  • July 25, 2012 at 1:47 pm

    #1517619

    Don't ever build a process to shrink data or log files. Manage them appropriately. The space you need today may be too small for tomorrow, so don't have a process unnecessarily shrinking things.

    If something has grown out of control, or with a rogue or one-time process, shrink it, but do it with thought. Not with a script or process.

  • July 25, 2012 at 2:53 pm

    #1517638

    I completely agree with Steve, shrinking without thinking can come back to haunt you really bad.

    Perfect example ==> Log grows over the weekend during a index rebuild or during a batch load and you shrink it later and then restrict growth on the file , the index rebuild job or the batch load is then bound to fail the next run when log runs out of space between 2 log backups and then you may end up with having some serious explaining to do.

    Therefore as Steve suggested a better approach would be to find the root cause of the growth and may be take alternative action if possible.

    For e.g. If you observe that the transaction log is growing significantly for a particular duration like say when a batch load runs it is better to add a specific log backup schedule that backups up the log more frequently to prevent exponential log file growth during that duration.

    Same may be the case with a data file as well but in a different way, a clustered index rebuild requires around 1.5 times of original size. Therefore even though you see free space available in the data files when in steady state, the database may run out of space during a index rebuild.

Viewing 3 posts - 16 through 17 (of 17 total)

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