April 19, 2009 at 8:25 am
Hello,
One of my database on Production Server which was 100GB and it grow 204GB on last weekend. Nobody use that server at weekend. I have setup maintenance plan for Backing Up, rebuild index and Integrity Check at weekend, nothing else. I don't understand why database is growing that big over weekend. I had that problem once last couple of weeks ago but I solved that problem using 'SHRINK DB' and database size is gone back to 100GB. Now happen again!!!
Anybody can help me how to fix that problem?
**In database properties, DB growth size by 10%.
Thanks.
April 19, 2009 at 9:57 am
Hello,
Is it the Data File(s) or Log File that grows? What Recovery Model does the Database have? How are you rebuilding the indexes (with DBCC DBREINDEX perhaps)?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
April 19, 2009 at 10:10 am
Leo (4/19/2009)
Hello,One of my database on Production Server which was 100GB and it grow 204GB on last weekend.
Is it your data file or log file?
Nobody use that server at weekend. I have setup maintenance plan for Backing Up, rebuild index and Integrity Check at weekend, nothing else.
Your maintenance plans are the culprits. Rebuilding index- what is the schedule? What is your recovery model set to while you are rebuilding indexes?
I had that problem once last couple of weeks ago but I solved that problem using 'SHRINK DB' and database size is gone back to 100GB.
Not a good idea, it grows again and you shrink again which leads to file fragmentation and which is a huge performance hit on your server.
**In database properties, DB growth size by 10%.
There you go this is a very bad setting. Suppose your data file is 100GB and if sql server needs to grow it will grow by 10% which is 10 GB(owsssss!!!!!!). Monitor your data file growth and dont set it in % rather set it at MB/GB values.
April 19, 2009 at 10:36 am
As mentioned above, it's likely the log file that's grown. Check the individual sizes, and do you have log backups running? If not, you need to set those up.
April 20, 2009 at 2:05 am
Hi Everybody,
My database Model is Set to Simple as we don't need to backup the Log. I got 2 Data File and 1 Log File. Log File size is reasonable about 14GB. Data_1.mdf is about 14GB.
Problem with Data.mdf which is 200GB.
I have never shrink the database because of Data Fragmentation. That's why I am using simple mode and Transaction Log is not really important for that server.
I do Index rebuild and backup at night.
By the way, did you guys mention about the Data Growth size? What should I do? What shall I change the 'Automatically growth file', by Percent OR In Megabyte? If I need to change it, then what size I need to change it?
** My RAID Drive Space is only 202GB where that DB is on and only '10MB' left at the moment.
Thanks again.
April 20, 2009 at 2:54 am
If you do a REBUILD INDEX of all your tables in a single process, you are likely to end up with double the database file size you really need. This is because no free space is released until all indexes have been rebuilt.
The best approach is to only rebuild those indexes that need it. You can examine the index statistics to find which indexes are disorganised.
If you do not feel you have the skills to do this, then generate a script to rebuild all your indexes on a table by table basis. Something like 'SELECT ''DBCC DBREINDEX ''' + name + ';' FROM sys.tables should give you a simple script but you may need to enhance this for your site. Because this script will process 1 table at a time, the space released when the first table has been processed can be used by the next table.
In this way you should find you only need enough free space in your database to hold a copy of the largest table plus its indexes. Because you will do this work every week, do NOT shrink the database after you have done the work. Shrinking the database will a) fragment the indexes you have just rebuilt; b) cause NTFS fragmentation which will harm your performance.
Finally, you need to put in place a plan to get more disk space because sooner or later this will be needed. Find out how long it will take from you asking your manager for the space to the time it will be made available to you. In many places this can take 3 months or more.
Then you need to do some capacity planning.
a) Look at your database growth patterns over a month or more and predict when your disk will be full.
b) Work out how full the disk would be 2 months prior to that. This should be your target maximum disk usage.
c) Work out how full your disk would be when you deduct from point b) your lead time to get more space.
d) When your disk has less free space than the figure you got in c), start the process to get more disk space. In this way you should never run out of disk space.
One place I worked in my management kept saying 'ask me next month' when I was asking for disk space because we had gone past point c. When they finally agreed to get the extra space there was not enough time to get it installed before we run out of space. The impact on our users was such that the next time this happened my management put the request through immediately!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 20, 2009 at 3:54 am
Hi EdVassie,
You are right. I keep asking for more drives for my production Servers but Management Team said 'YES' but it was last 2 months ago I am still struggle fixing the Space and all sorts. I hope I will get new drives end of the month.
During that time, Shall I
-Stop doing Rebuild Index through Maintenance Plan? and do it as you said, do it by Table level?
-How do I fix current space problem. I got 10MB left. Shall I shrink Database anyway and rebuild the index by table level? so that will be sort out space and fragmentation.
-What option shall I use for Auto Grow Level?
Thanks.
Leo
April 20, 2009 at 5:51 am
IMHO you should stop the index rebuilt in the maint plan and do it table by table.
After this has been done, look at the size of your largest table and add this amount to the space used in the DB file value. Add maybe 20% to allow for some growth then do a SHRINKFILE to this new value. Set a reasonable growth value (perhaps 10GB).
Things may need tweaking for your site, but you should now find your index rebuilds do not cause the database file to grow each week. You will also be able to monitor DB file use. You can either let the file autogrow, or take pre-emptive action and manually add another 10GB when your file is about 80% full.
This type of action should help you show to your manager that everything that can reasonably be done to use disk space efficiently has been done. Mail your manager with your prediction (+/- 3 months) for when the disk will be full (email this, don't just tell them!). If they do nothing and the disk does get full, at least you can show evidence you did all that could be expected.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
April 20, 2009 at 6:50 am
Hi EdVassie,
Thanks a lot.
Leo
June 18, 2010 at 3:38 pm
I know this is an old question, but I found it via Google, and others might too, so here's some info for them.
If you want to defragment your indices as suggested above, there are many scripts on the web, some better than others. The best I have found is the one at:
http://msdn.microsoft.com/en-us/library/ms188917.aspx
The page also has a lot of useful info on how and why to manage fragmentation.
Look towards the end of the (long) page for the section called
D. Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes
Run that script to defrag the indices that need it, one at a time. Don't forget to precede it with a USE statement as it advises.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply