CODEWITHSUNDEEP
sql-servermdfvarbinarymax
jdf35w
jdf35w

Reputation: 29

SQL Server MDF file grows much too big for the amount of data inserted

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?

Upvotes: 2

Views: 2302

Answers (1)

md4
md4

Reputation: 1669

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.

Upvotes: 2

Related Questions