June 13, 2003 at 10:29 am
I have a simple table with an id field and an image field. I loaded five records, each containing a 2 meg .bmp file. sp_spaceused is reporting 20 megabytes of data, even though I have only loaded 10 megabytes worth.
Can someone explain the difference?
Thanks,
Chris
June 13, 2003 at 12:33 pm
Can you post the result of sp_spaceused @objectname = 'yourtablename', @updateusage = true? Any indexes created for this table?
June 13, 2003 at 12:43 pm
Here you go. I have since added a sixth image but it was tiny. There is still about 10 megs worth of data in this table. I am also including the output of sp_help. No indexes or anything like that. I am still in development stage at this point and noticed this.
Thanks,
Chris
sp_spaceused image_test, @updateusage='true'
--------------------------------------------
namerowsreserveddataindex_sizeunused
image_test6 22104 KB22048 KB8 KB48 KB
sp_help image_test
--------------------------------------------
NameOwnerTypeCreated_datetime
image_testdbouser table2003-06-11 11:00:35.080
Column_nameTypeComputedLengthPrecScaleNullableTrimTrailingBlanksFixedLenNullInSourceCollation
image_IDintno410 0 no(n/a)(n/a)
imageimageno16 yes(n/a)(n/a)
IdentitySeedIncrementNot For Replication
image_ID110
RowGuidCol
No rowguidcol column defined.
Data_located_on_filegroup
PRIMARY
The object does not have any indexes.
No constraints have been defined for this object.
No foreign keys reference this table.
No views with schema binding reference this table.
June 13, 2003 at 1:07 pm
Which version of SQL Server and Service Pack are you running?
June 13, 2003 at 1:14 pm
SQL Server 2000 SP2
Another interesting tidbit is that when I bring the image back out it's double in size.. so there is definately bloating going on somewhere, and I am hoping it isn't with SQL Server.
I am starting to suspect that this has something to do with how I insert the data in. I am in the process of testing that right now.
Pretend that you didn't hear that though, because I could be wrong! 🙂
Chris
June 13, 2003 at 2:51 pm
Never mind! I screwed up... There is a strange behavior in the re-serializing of the picture which bloats, however storage is behaving like it should..
My screw up completely in that inserted one picture.. took it back out (which doubled it in size) and then I wiped the table and re-inserted the picture that was double in size. So I thought I was putting in a 2 meg pic when in reality I was putting in a 4 meg pic..
My bad, sorry for the mixup. I will investigate on why this bloating occurs when reserializing, however that doesn't have anything to do with SQL Server so I'll seek help with that one elsewhere.
Thanks,
Chris
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply