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
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?
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.
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.
20 People are following this question.
