October 19, 2007 at 10:05 am
I have a production database that is 28000MB but it has only 4MB of data in fact, so it's mostly empty. I cannot estimate how much it will grow. I was advised to shrink it and I wonder what are advantages and disadvantages of shrinking. If I shrink it too much, there will be performace hit every time the database automatically grows again. At the same time having 4MB of data spread across 28000MB of file also causes a performance decrease. Mostly I am afraid to ruin live database perfomance.
My friend advised me to do the following:
1. Right click the database and select Tasks, Shrink, Files
2. Click Reorganize Pages before releasing unused space
3. Type in what size you want to shrink the file to. I put in 100 MB but as you can see your data is only actually using 4MB
4. Then do the same again but select Log from the file type drop down again I chose to shrink to 100MB.
Once that has run you can see that your database is now only about 500MB rather than 28000MB
I would suggest that you ask someone with a bit more SQL knowledge than me what the best size to shrink it to would be. As I understand it the reason the files have become so big is that it does not re-use the empty space in the file and instead keeps expanding the file by 10% every time it needs a bit more room. Now if you think about what will happen when your database is 10000MB and it wants to put 1MB of extra data in the file. It will increase the size of the file by 10% i.e. 1000MB when it only actually needed 1MB.
As I understand it once you have shrunk your database to a sensible size you should set the MAXSIZE to a bit more than you ever think you are going to use. For instance if your think your database will grow to a maximum of 10MB you should set MAXSIZE to 15MB
So what should I do, to shrink or not to shrink and to what size... And how to make database to "re-use empty space in the file "
October 19, 2007 at 10:25 am
- first of all start with making a full database backup
- you state it only contains 4Mb of actual data.... How long is it in use ?
- how much actual load do you expect within now and lets say one year ?
If you're not sure, I'd go for datasize 100Mb, log size 25Mb and grow both with 10Mb (Not in percentages).
Maybe this is still oversized, but at least not 28Gb oversized.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 19, 2007 at 10:28 am
Hi Vika,
First of all, try to find out why the database grew up to 28GB with only 0.5GB used. Is it used as some sort of staging storage for periodic data upload which is cleaned up after the processing is done? Or was it just a one time process that dealt with a lot of data and would not happen again?
Once you figure that out you can decide if you need to shrink it and to what size. The steps you have are fine. Try to specify the target size to be 25-50% more of what's used now.
As for auto growth you can change the increment from 10% to a reasonable absolute size, e.g. 50MB or 100MB or so. If it's SQL 2005 you can greatly speed up auto growth by using "Instant File Initialization". Check BOL for instructions on how to turn it on.
Regards.
October 19, 2007 at 3:15 pm
Your concerns with auto growth causing a performance hit are correct. Also, you likely do not 28GB data file for 500MB. I agree with all of bbychkov's comments (investigate why, shrink to 25-50%, set auto grow to an absolute size). I would recommend you follow those suggestions. To add to his suggestion about trying to find out why this go so big with so little data, I would first take a look at the transaction log. Are you backing it up?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply