SP_SPACEUSED is giving wrong counts !!!

SP_SPACEUSED: is a system stored procedure and used to capture number of rows, total size, data and index size information. Sometimes SP_SPACEUSED may give incorrect information or SP_SPACEUSED  give wrong counts. Below are some of the reasons those may leads to give the wrong page / row counts in SP_SPACEUSED. This may because of below reasons.

1. Statistics are not updated for a long time

2. Index maintenance is not being organized

3. Huge number of DDL operations

If you find a wrong result from SP_SPACEUSED, follow the below instructions to resolve this issue.

1. Run DBCC UPDATEUSAGE

2. Rebuild indexes

DBCC UPDATEUSAGE: It reports and corrects inaccurate page and row count information in catalog views. These inaccurate values may impact the result of SP_SPACEUSED.

“DBCC UPDATEUSAGE” corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index.

This command can run over a specific database, table or index.

Example:

USE udayarumilli;
GO
DBCC UPDATEUSAGE(0);

Run DBCC UPDATEUSAGE on entire database:

USE udayarumilli;
GO

-- Running command on entire database "udayarumilli"
DBCC UPDATEUSAGE('udayarumilli');

Run DBCC UPDATEUSAGE on a specific Table:

USE udayarumilli;
GO

-- Running command on a specific table "Product_NewID"
DBCC UPDATEUSAGE('udayarumilli','[Product_NewID]');

Run DBCC UPDATEUSAGE on a specific index:

USE udayarumilli;
GO

-- Running command on a specific index "ix_Product_NewID_ID"
DBCC UPDATEUSAGE('udayarumilli','[Product_NewID]','ix_Product_NewID_ID');

Precautions:

1. It’s not supposed to run this command on frequent basis on sql instance with 2005 or later versions.

2. Can execute onetime if we find issues with SP_SPACEUSED results

3. Can schedule to execute weekly basis only if database undergoes DDL modifications.

 Have a look here for more details.