I've got the following SP which I wrote to calculate the size of all the tables on my database
SELECT name,dbo.nice(rows) as [rows], dbo.nice(CAST(total-indexes as DECIMAL)/1024) as [data (MB)], dbo.nice(CAST(indexes as DECIMAL)/1024) as [indexes (MB)], dbo.nice(CAST(total as DECIMAL)/1024) as [total (MB)] FROM ( Select A.name,rows ,SUM(CASE B.indid WHEN 1 THEN B.used ELSE 0 END)*8 as [total] ,SUM(CASE B.indid WHEN 1 THEN 0 ELSE B.used END)*8 as [indexes] from sysobjects A (nolock), sysindexes B (nolock) where A.id = b.id and A.type = 'u' and rows > 0 group by A.name,rows ) as [d] order by total desc
I can't recall where I got this exactly. I wrote the outer part and found the inner part online somewhere.
Anyway, it does the trick. It calculates the size of my data by subtracting the indexes. I got a shock today when I ran the SP and compared it to a result from a few days ago
table rows data (MB) indexes (MB) total (MB) MyBigTable 240,106,454 35,428 33,001 68,429 // 4 days ago MyBigTable 241,442,655 57,128 11,559 68,688 // today
My data had jumped in size! After inspecting the SP source code and remember how it works I realise of course the data size did not change (much). What happened was the indexes drastically reduced in size.
What causes this? I didn't make an edits to the indexes. I didn't shrink the DB or run anything on it that should affect the indexes. Why did my indexes decrease so dramatically? And why didn't it affect the overall size of the database?
I looked into my indexes and found one was seriously fragmented, at over 80%. I rebuilt it and now the data has jumped again. Here are the new stats.
table rows data (MB) indexes (MB) total (MB) MyBigTable 240,106,454 35,428 33,001 68,429 // 5 days ago MyBigTable 241,442,655 57,128 11,559 68,688 // yesterday MyBigTable 241,705,941 35,581 32,538 68,119 // today