Today, I came across a surprising situation when running sp_spaceused on one of my dummy table “Tblemployee”. The SQL Server sp_spaceused told me that it has over 200,000 records, however it returned 0 records when I ran “Select Count(*) from tblemployee” as shown in below snapshot.
and even more surprising was that it didn’t get rectified after running DBCC Updateusage and @updateusage=’true’ as shown in below snapshot.
I then checked the table indexes and found that there was a clustered index on EmpId column. Rebuilding it rectified the sp_spacedused proc and it gave correct result as shown in below snapshot.
Problem solved .
Like us on FaceBook | Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook
Hi Ahmad,
So why it was giving a wrong information before and why it rectified correct information after rebulding indexes.
Thanks,
Shankar..
Hi Shankar – It gave wrong information as the underlying catalog/views which are being used by sp_spaceused procedure weren’t accurate. The dbcc updateusage must have rectified the inaccuracies however somehow it didn’t. Rebuilding indexes rectified those inaccuracies and sp_spaceused gave correct results.
Thanks,
Ahmad
I believe that if the table had been previously truncated instead of “deleted from”, that would have also reset the space used count.
It’s not that it’s returning the wrong information, it is “using” that space in that it’s allocate to that table as is not available for other tables to use. Rebuilding the index frees up the used space.
Have a look at “SQL Tuning” by Dan Tow, it’s got a good chapter on how the space is allocated as the data is added, updated and deleted.
Hi SQLChap – Thats what I explained to Shankar. As per me .. its just another way of quoting the problem…
Thanks,
Ahmad
HI Ahmad
Thanks for this. I was going crazy, or thought SQL was…. this happened to me also, in validation of new functionality I am developing, on SQL Server 2008 R2 SP1…
So, yes, Heisenberg’s Theory of Uncertainty must now include a factor for the tool used to make the measurement!
Thanks for documenting your findings so well.
sp_spaceused uses sys.partitions.
sys.partitions catalog will return rows which are only approximate. This would be more significant for tables which are undergoing frequent insert/deletes. For an accurate value, we should always use COUNT_BIG().