Solved

shrink database - reclaim space, but leave room too

Posted on 2006-11-16
6
586 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Accepted Solution

by:
regbes earned 500 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 500 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

862 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now