Results 1 to 4 of 4

Thread: Unused space

  1. #1
    Join Date
    Jul 2004
    Posts
    268

    Unanswered: Unused space

    How can I determine how much unused space to remove from database files? I am setting up a maintenance plan and I am trying figure that out.
    Also, is there a rule on how far to shrink TL?

    Please help. Thanks.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Quote Originally Posted by inka
    How can I determine how much unused space to remove from database files? I am setting up a maintenance plan and I am trying figure that out.
    Also, is there a rule on how far to shrink TL?

    Please help. Thanks.
    You want to consider these things carefully before you go implementing them in your maintenance plan. I usually ignore the shrink settings in the maintenance plan, particularly for the TLs.

    For data files, as a general rule I like to keep about 20% free space and let them auto grow up to about 15 GB. once past this threshold, I usually turn off auto grow and manage the growth manually during scheduled maintenance windows. I usually do not shrink my dbs unless I do a major data purge that frees up a very large chunk of space. Expangding a datafile is a very costly activity and can hold up insert/update statements. Why routinely shrink a db that is (generally) going to grow in size anyway?

    For transaction logs, I like to try to keep the physical size relatively constant and schedule log backups to occur frequently enough to prevent the log files from filling up completely. Same reasoning here: the process of expanding a log file is expensive in terms of host resources and it is something I would prefer to avoid.

    If you are talking about small (<100 MB) databases on development servers, the story might be different. But in that case, why would you need to conserve the space? Performance does not improve with file shrinkages. It's not like running a defrag...

    Regards,

    hmscott
    Have you hugged your backup today?

  3. #3
    Join Date
    Sep 2002
    Location
    London, UK
    Posts
    3
    If you do a major data purge, how do you then shrink the mdf? I've run DBCC SHRINKDATABASE, but I still have nearly 4 GB of unused space.
    Thanks,
    poexda

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by poexda
    If you do a major data purge, how do you then shrink the mdf? I've run DBCC SHRINKDATABASE, but I still have nearly 4 GB of unused space.
    Thanks,
    poexda
    That can happen due to many issues,better read this one first -Link
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •