nespa
asked on
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?
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?
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.
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.
ASKER
> 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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
you need to take regular backups of your log files, usually a lot of space is needed for the log files