2

I have a table which stores binary data. It has 490,409 rows. When I sum the datalength of the binary varbinary(MAX) column, the total size comes to about 12gb.

However, every time I insert new rows, the .MDF files grows in GBs, even when I'm inserting just a few hundred MB. Auto Grow is set to 5%. When I run sp_spaceused on the table it shows 0k unused. And when I run that against the DB, I get 345.41 MB unallocated out of 16320.56 MB size DB.

What am I doing wrong that the .MDF files grows so much every time I insert even a relatively small amount of data into this table?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
jdf35w
  • 29
  • 2

1 Answers1

2

By a basic calculation (16320.56 × .05), that's 816MB, which is fairly close, and if you factor in any additional indexes on other columns in that table, then it could account for the extra growth size.

I personally would ditch percentage-based autogrowth anyway, especially so if this is bothering you, and go with a fixed size growth increment.

md4
  • 1,609
  • 11
  • 13
  • The index is very small. I don't get where the 4gb comes from... What would you suggest for a fixed growth size? – jdf35w Sep 26 '14 at 16:46
  • Ideally, I would suggest pre-allocating the amount of space you think the database will need and disabling auto-growth entirely. But sometimes that isn't an option. I usually default to 1GB, but it's highly dependent on how fast the database grows and what your hardware is like. You're aiming to balance 2 competing factors: large enough increments to reduce the amount of growths, but not so large that performance is impacted. – md4 Sep 26 '14 at 23:03