Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

shrink database - reclaim space, but leave room too

Posted on 2006-11-16
6
Medium Priority
?
596 Views
Last Modified: 2008-01-09
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?

0
Comment
Question by:nespa
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17963124
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
 
LVL 11

Expert Comment

by:regbes
ID: 17963269
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
 
LVL 1

Author Comment

by:nespa
ID: 17965220
> 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 11

Accepted Solution

by:
regbes earned 2000 total points
ID: 17966396
>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
 
LVL 11

Assisted Solution

by:regbes
regbes earned 2000 total points
ID: 17966403
also all that auto grow and shrinking may fragment the device causing thrashing
0
 
LVL 1

Author Comment

by:nespa
ID: 18021260
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question