question

shridhar avatar image
shridhar asked

sql server show more size than number of rows

I have a Sql Server 2008 database that has a one tables with 10000 row count but take up a combined 5 GB of space. I can see this by doing right-click/properties on the tables in question (data space is huge, between 1 and 6 GB, and row count is 10000 on these tables). I have no clue what could be causing this as I would assume zero rows would mean nearly max .5 mb space taken I can not truncate or recreate this table cause this is my live application
sqldatabasesql server 2008 r2memorysize
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Forgive me, I'm confued. You have 10K rows but thought you had none? Why is zero rows taking up 5GB worth of space? Depending on how the table is structured (LOB's?) & what data it is actually storing I could see it taking up that amount of space (depending). What is being stored within the table?
1 Like 1 ·
IgorMicev avatar image
IgorMicev answered
What is the Size and Space Available? You may have a lot of space available. The other option is if you have LOBs in your tables.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
Don't forget that indexes also take up space. Is this a heap, or is there a clustered index? You might be seeing problems with forward pointers. https://blogs.msdn.microsoft.com/mssqlisv/2006/12/01/knowing-about-forwarded-records-can-help-diagnose-hard-to-find-performance-issues/ You might also be seeing space used up with badly fragmented indexes.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.