Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have already asked some questions about Autogrowth and shrink. But I have some doubts related to it.

1)I have a database which grows about 300 MB every month. So is it best to set Autogrowth of 300 MB to it? Now 10 MB is set.

2)I have so many small databases which really have data of about 50 MB. But it's physical size is about 900 MB.(So 850 is free space). I have not shrinked this database. As if i shrink it then again when data gets added autogrowth occurs. But adding data to this database is rare. So I think it will not gro more than 200 mb for next one year. So should i shrink the database? If I keep it as it is Should that cause any performance problem? Or having more free space(more than 90%) in database will cause any problem. I think most of the people think having large physical size will cause performance problem. So is it true or not?

3) SQL Server 2008 R2 Express Database Size Limit is 10 GB. So is it physical size of both mdf and ldf file together? because I need to consider this if i should shrink database at any point.

share|improve this question

2 Answers 2

up vote 3 down vote accepted
  1. Yes, picking an autogrowth size that makes sure you minimize or eliminate the number of growth events during your business cycle is ideal. I would venture to say that if 300 MB per month is that predictable, you should also - in addition to setting more reasonable autogrowth settings - to grow the file right now by 4 or 8 GB to allow for one or two years of growth without having to worry about interruptive growth events (even with instant file initialization, data file growths still cause transactions to wait while they complete). If you know you're going to grow by about 300 MB per month, why put off all those growth events? What are you going to do with the space in the meantime? Lease it out month-to-month?

  2. You are probably fine shrinking that specific database to 200 MB. Be sure to do this with DBCC SHRINKFILE or ALTER DATABASE MODIFY FILE, not DBCC SHRINKDATABASE.

  3. The Express limitation is on the data file only. You can have a 9.99 GB data file and a 600 GB log file, and SQL Server won't complain. Well, it will in other ways, but not due to exceeding any arbitrary file size limitation. That said, you should either make sure to be in simple recovery or back up your log at a reasonable rate. Log files should never be growing uncontrolled - and if you're worried about the log file on an Express instance, something isn't right. Please see the following posts for plenty of discussion:

share|improve this answer

the Auto shrinking or manual shrink does not yield benefit every time , it may be counterproductive please refer the below link

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

Also it is good estimate size of the db beforehand as you have done.However it would better if you calculate for longer duration.instead of repated growth every month

share|improve this answer
    
Can you please answer my second question? –  IT researcher Jan 22 at 9:30
    
Having free space does not cause any performance issue in the database,(though i do not have any citation for this) but shrinking does have negative effect as per the link i have shared earlier.Instead of looking into db size it would be good to look into fragmentation if it exists. –  sqlengineer Jan 22 at 9:44
    
Yes. i know that shrinking is bad and must not be done. But i was interested in knowing whether large free space will cause any problem.thank you. Also wat about my 3rd question? –  IT researcher Jan 22 at 9:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.