CODEWITHSUNDEEP
sql-serverimage
Rob
Rob

Reputation: 10258

SQL Server 2008 Varbinary(Max) column - 28Mb of images creating 3.2Gb database

This is the first time I've tried to store images in my DB instead of the file server and I'm regretting it so far. I can't use filestream because my host doesn't support it so I'm using a varbinary(max) column. I'm keeping track of the image sizes I insert and there's about 28Mb so far but the database is at 3.2Gb which is just crazy. Am I better to varbinary(XXXX) to reduce this - is SQL Server reserving space for the MAX?

Using MS SQL Server 2008 btw

Here is the top Table sizes:

TableName   RowCounts   TotalSpaceKB    UsedSpaceKB UnusedSpaceKB
Municipality    1028316 64264   64232   32
Image   665 33616   33408   208
User    320 248 224 24
SettingUser 5910    264 160 104
Region  1418    136 136 0
ImageUser   665 56  56  0
ConversationItem    164 56  56  0
Setting 316 48  48  0
Culture 378 40  40  0
UserTrack   442 40  40  0
Numbers 1000    32  32  0
Country 240 32  32  0
Conversation    52  32  32  0
CountryIp   0   88  32  56
ReportUser  0   16  16  0
ConversationItemImage   0   16  16  0

Here's the result for exec sp_spaceused:

database_size   unallocated space
3268.88 MB  0.84 MB

reserved    data    index_size  unused
359592 KB   291744 KB   66600 KB    1248 KB

I should probably also mention that there is a Geography Column on the Municiplity Table too in case this has any impact due to spatial indexes... I've used this plenty of times in the past and had no issues but I've never had 1M+ records either usually less than 20k

Upvotes: 2

Views: 573

Answers (1)

ESG
ESG

Reputation: 9435

Make sure that all that space is being used by the actual data, and not the log file.

Shrinking the log file will only remove unused space. In order to clear entries before shrinking it, you would need to backup or truncate the log before hand (Warning: If you care at all about your log chain, this could break it)

Upvotes: 2

Related Questions