Solved

shrink database - reclaim space, but leave room too

Posted on 2006-11-16
6
587 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

832 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