shrink database - reclaim space, but leave room too

i'm shrinking a database which brings my 9GB db to 3GB (lots of extra tables were truncated since they're not needed...and shrinking reclaims the space).

however i understand that's not always desirable; sometimes it's good to leave space (that can be filled in by new records later).

BUT - if you don't reclaim the space (and in my case above) you're left with the larger database even though you want to slim it down some.

is there a happy medium?   is there a way to determine how much page space to leave so that you don't have as many fragmentation issues?

LVL 1
nespaAsked:
Who is Participating?
 
regbesConnect With a Mentor Commented:
>in this case, wouldn't it make sense to shrink every time that happens?  
IMHO if you need the space for something else yes
if you dont need the spce why bother it wil grow to a max stable size then settle down it seems to me that you will just be adding steps to your job taht at best will add load to your server
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Once in a while it is ok, but shrinking the db regularly is not advisable.
you need to take regular backups of your log files, usually a lot of space is needed for the  log files
0
 
regbesCommented:
Hi nespa,

I take the view that if the server does not need the space do not shrink the devices.

what is the point of having 50gb of free space vs 60 gb of free space if the space is unsed its unsued there is no pratical use to having free space that is not required.


HTH

R.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
nespaAuthor Commented:
> you need to take regular backups of your log files, usually a lot of space is needed for the  log files

good point - yes, i'm cycling the files as mentioned in my other question.   thanks!

> what is the point of having 50gb of free space vs 60 gb of free space if the space is unsed its unsued

well, this DB is one of 20.    so 9G = 180G, whereas 3G = 60G   (obviously they're not all the same size, but that's the gist).    there are DTS jobs running against this DB that are leaving large sets of data in the DB and i'm planning on having that data offloaded (as bus. rules require us to keep the data) so that it doesn't affect DB size.   then i'd like to reclaim that size.  

in this case, wouldn't it make sense to shrink every time that happens?  
0
 
regbesConnect With a Mentor Commented:
also all that auto grow and shrinking may fragment the device causing thrashing
0
 
nespaAuthor Commented:
sorry i left this question open.

i see - it's true that loading the DB every so often will always cause the db to grow...that is, shrinking it often won't necessarily help...best to truncate the tables if not needed & leave that free space so that it can be used / filled up by temporary data that will then be truncated.

thanks for the input on this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.