SQL Server Performance Forum – Threads Archive

DataFile Growth

I have just recently moved a database from one machine to another and the datafile is growing a lot faster now. The database was around 2gig before and was growing but at a alot slower pace than what it is now. In a matter of three weeks it is about 14gig. I suspect it has something to do with the disk sector size, but it does not make sense as they were formatted with the reccommended 64K units. The only difference is that the old datafile was on a RAID5 and the new one is on RAID0. Your help would be most appreciated.

Hi, what is your recovery model ? are you doing bulk operation ? is your application write intensive ? are you performing index defreg / re index as your maintanance plan ? <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

Hi ghenant,
Thanks for your reply.
I have been running on simple recovery up until two days ago where I have changed it to Full. No bulk operations are performed. I reindex all tables once a week as part of my maintenance plan. ( As I understand it you don’t have to run index defrag if a re-index is done). Last I checked, the app reads/writes are approx. 80/20. It is a fairly high usage site, but the perplexing thing is it was running fine before moving to the new setup, nothing dramatic has been changed. I might try re-formatting the drives to 8k to see if this makes a difference, but seems strange that on the RAID5 it was 64K and not having this problem. What do you think? Regards.

Hi, have you take a full backup of your database after change in Recovery Plan ? it is advisable to take a full backup before and after change of recovery mode ! well as you change you recovery mode to full . what is your AutoGrow value set to ? in % or fixed MB ? say if you have set it to 10% and your DB size is 2 GB now when it has to increase or need room to expand it will then increase 200 MB everytime it need more space and also while you re-index . Refer below thread HTH : http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=10058</a> http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=11586</a> <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Regards Hemantgiri S. Goswami [email protected] "Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami

Ghemant,
Yes i have taken a full backup of the database after setting the recovery model. The growth is set to the default autogrow 10%. The size of the Db is currently 25g, 24.6g data file with 11.4g free space and the ldf is a mere 500mg. I understand the growth also occurs when reindexing or backing up is done, so explains the large space, but it does don’t explain why the actual data is growing at this rate. I am not experiencing any performance problems or anything adverse, just concerned with this growth- it’s just not normal. I still am inclined towards thinking it is something to do with the disk allocation size. 8k page being writen into a 64k sector, therefore 56k free and taking up the disk space.//?? Thanks for your time there Ghemant.

hi, Can you observe the data insertions and updations. You can compare the periodical status of database sp_msforeachtable ‘sp_spaceused "?"’ you will get the records of each table and its size wise report. compare with a fixed period.
You will get the records added with in this period and the size increased too.
If this not a comparable, then you have to look into that. -Johnson

Hi, Everything is okay now, except I feel a little stupid. I should have done a check, as suggested, and done a comparison. The cause of the size increase was caused by data and there was a job that did a daily delete of some temporary data that was disabled! At least it was something simple. Thanks guys.

If you need to get accruate information on the table sizes then run SP_SPACEUSED @UPDATEUSAGE=’TRUE’ for accurate information (or schedule a job periodically). http://vyaskn.tripod.com/track_sql_database_file_growth.htm has a good information in this regard.
Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>