mdf file growth - no inserts

Question

mdf file growth - no inserts

Viewing 6 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

free_mascot One Orange Chip Points: 27164 More actions · Answer 1

Ryan Keast (4/7/2014)
From ih -
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,1011704 KB,512424 KB,498648 KB,632 KB
From ih_restore
name,rows,reserved,data,index_size,unused
em_comm_costing,384191,119808 KB,62960 KB,56088 KB,760 KB

Any change in index?

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

Page splits in your indexes?

As there is no increase in rows, is it possible some are being update or replaced?

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 3

Looks like it's linked to my Rebuild Index Task.

Everytime I run it - the database balloons in size.

Via the GUI I have the Change Free space per page to 90%.

So is it normal for the database to double in size whilst the reindexing is happening as it got to about 106 GIG (the db is 48 Gig) before I ran out of space on the server.

chetanr.jain Hall of Fame Points: 3938 More actions · Answer 4

During Rebuilding of index, First index creates a copy of the exisiting index (for backup purpose) and does the rebuilding process.

Are you doing rebuilding process for all index or selected index ??

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

1) It could be caused by HEAP table activity where you insert and delete rows over and over or update rows to be larger than they currently are.

2) By specifying 90% FREE space you are making each page only 10% full. That WILL explode out your table/index sizes!

3) Are you perchance using GUIDs?? They can lead to massive index fragmentation and extra space usage.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Jeff Moden SSC Guru Points: 1004664 More actions · Answer 6

Ryan Keast (4/7/2014)
Looks like it's linked to my Rebuild Index Task.
Everytime I run it - the database balloons in size.
Via the GUI I have the Change Free space per page to 90%.
So is it normal for the database to double in size whilst the reindexing is happening as it got to about 106 GIG (the db is 48 Gig) before I ran out of space on the server.

For really large tables, I've found that changing the Fill Factor to 90% is pretty much a waste of space. If the clustered index follows the best practices of being narrow, unique, ever increasing, unchanging, then you'll not very often have to worry about page splits unless you have variable length columns that start off empty or skinny and get updated to something larger.

As for the growth in the reserved but not used area that you speak of, like someone else said above, the old index is preserved until the new index is commited for any table over 128 extents (which is only 8MB). That means that you'll have at least 110-120% growth equal to your largest clustered index during an index rebuld of the clustered indexes. If you don't have SORT IN TEMPDB = ON for the rebuild, you'll use even more space in the given database.

If you have a bunch of very wide non-clustered indexes on the table, they can be nearly as bad as the clustered index. If you're using the ALL switch during your rebuild of all indexes on a table, you stand a pretty good chance of increasing your database size by 110-120% of all the indexes on that table including but not limited to the clustered index.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)