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.

1_SQL_Server_sp_spaceused_returns_wrong_count

and even more surprising was that it didn’t get rectified after running DBCC Updateusage and @updateusage=’true’ as shown in below snapshot.

2_SQL_Server_sp_spaceused_returns_wrong_count

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.

3_SQL_Server_sp_spaceused_returns_wrong_count

Problem solved .

 

Regards

Ahmad Osama

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook