Solved

shrink database - reclaim space, but leave room too

Posted on 2006-11-16
6
591 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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
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…

728 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