Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
miranwar
Posting Yak Master
125 Posts |
 Posted - 2006-11-13 : 06:09:46
|
| Need some help in explaining why the data file has grown unexpectedly? On Friday the stats were as follows:Total Space: 14675.62MBUsed Space: 13209MBFree Space:1466MBDatabase was set to grow automatically at 20%On the week end I turned of AutoShrink and ran a Re-index Job. This Morning I have discovered that the database has grown dramatically and I don’t know why.Here is the new database stats for the data MDF fileTotal Space: 21132MBUsed Space: 13210MBFree Space:7922MBCan someone shed some light on why the database has grown so dramatically??Thanks |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-13 : 06:14:43
|
don't autoshrink the db. this means it gets fragmented.when you rebuilt the indexes they got properly placed (they aren't fragmented) in the file and so it grew your file.this is good.Autoshrink is bad.  Go with the flow & have fun! Else fight the flow  blog thingie: http://weblogs.sqlteam.com/mladenp |
 |
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2006-11-13 : 06:18:17
|
| But the unused Space has grown by 6 Gig and the used space has grown by 1MB??? |
|
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2006-11-13 : 06:25:07
|
| try defragmenting your disk.well the database seems to need it... I have no idea how to check why it has grown so much.I hope someone will drop by and shed some light if it's possible.Go with the flow & have fun! Else fight the flow blog thingie: http://weblogs.sqlteam.com/mladenp |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 06:53:01
|
| "Can someone shed some light on why the database has grown so dramatically??"Its what I said in your other threadhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74581#26\8082This is the problem with using REINDEX. It moves the table to spare space in the file, making a complete copy of it before releasing the space from the original table. Its compounded if your TLog backups are infrequent because the log space will not get cleared down very often, and therefore more is needed.So in your case Reindex has added 6.5GB to the size of the database file in order to have enough room for this shuffling around. And then it freed up the space used by the original, un-optimised, table. So you now have 6.5GB free.Next weekend it will need 6.5GB again, but now you have removed the AutoShrink it won't need to extend the file (unless the data has grown of course)."Database was set to grow automatically at 20%"I strongly recommend that you use a fixed size, and not a percentage. Your database is currently being extended by 2.8GB (so in fact may have only needed to grow by 6.5GB - 2.8GB = 3.7GB). When that happens I would expect all connected users to get timeouts for maybe as much as 30 minutes. But I already described you that in http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74581#268057Kristen |
|
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2006-11-13 : 08:21:07
|
| Thanks kirsten..Ok that makes sense I thought from the previous posts the re-index would make the transaction log grow only and as the database is on Simple recovery model the log would be flushed at Checkpoint on the nightly backups. I will leave the data fils size as it is. Correct me if i am wrong from what I have understood apart from the performance overhead shrinking the db again would revert the re-indexing as pages would have to be shufflled to the begining of the file again which would cause fragmentation although no to the same degree as Inserts/Updates/Deletes.With regards to the growthrate at 20%. With this setting the DB i set to grow at 4.1 GIG when it runs out of space (20% of 21132MB). Surley if i reduce this to lets say 150MB at a fix rate. This will again increase file fragmentation as the file is growing in smaller chunks. What would you think would be an acceptable amount of growth so that neither performance or file fragmenation are risk. It's this trade off thats got me stuck and I'm not sure how to proceed. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-11-13 : 09:25:36
|
| "Simple recovery model the log would be flushed at Checkpoint on the nightly backups"Actually on Simple Recovery Model the log will be flushed whenever you hit a checkpoint, it doesn't have to wait for a backup. So you are only at the mercy of your biggest transaction (but if you have a very large table then a REIDNEX on that will be a Big Transaction! - and its possible that the whole Maintenance Plan job runs as a single task, so the Reindex of ALL tables is a single transaction; I don't know if that is the case, or not.The fragmentation is two fold. Firstly moving the table back to the front of the file, as you say, but there is a chance that they will be moved into a contiguous block, so that might not be an issue. Then there is the need to re-acquire the disk space all over again. That is much more likely to cause fragmentation over time as the disk space [freed by Shrink] gets use for backup files etc. and then is no longer available as a contiguous piece when SQL Server wants to extend the DB.Also you can defrag a database physical file so that it is contiguous. That is worth doing once in a while, but then if you Shrink it and it grows back again you've lost the benefit - until the next time you de-frag the physical file, of course.See sysinternal's utility "contig" if you want to defrag a single database file.The amount you set for file extension is a trade off. Yes it will fragment the disk by allocating smaller pieces, but it will allocate it quickly. It is better for the Business to allocate a smaller file extension, more often, so that SQL Server can allocate it quickly, and not cause any timeouts, than a bigger extension that is more likely to be contiguous but risks timeouts.Also if you allocate a 2GB extension to a 10GB database that's quite a significant additional resource hog whenever you ahve to Restore the DB - that extra 2GB has to be initialised (well, if you are creating a New database, at least, not sure about if you restore to a pre-existing database)You can sort out the fragmentation with a physical file defrag periodically.Your Reindex is probably the only thing you have running that needs more than 150MB extension in a day. Monitoring the Space Used in your database will tell you roughly how much "real growth" you get. One extension per day (leaving aside the amount that Reindex greedily grabs; it won't care if the database adds its space in smaller or larger pieces) is fine. In fact two or three a day would be fine. You don't really want 1 or 2 extensions a minute though!!I suspect the 20% growth is responsible for period dramatic slowdown of your database, which I think you reported in earlier threads. In the main I expect that the Reindex is grabbing so much space on a Sunday night that there is no need for an Extend during the week. However, when you had the Shrink in place then you WOULD have needed an Extend during the week, and that 1 or 2 GB extension would have taken probably 30 minutes and caused timeouts. So go for 150MB. If possible track how often the DB is being extended (deliberately shrink it to force this to happen as a one-time experiment if you like, then you can just watch the file-size hourly and see how often it changes over time - it won't be any slower than before!!).Kristen |
|
|
|
miranwar
Posting Yak Master
125 Posts |
Posted - 2006-11-13 : 11:49:27
|
| Ok thats Cool...I'll experiment with setting the autogrowth to a set limit. I think I'm begining to understand this now.Once Again THANKS AGAIN!!!!! |
|
|
|
|
|
|
|
|
Unexpected Database Growth