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 .