SP_SPACEUSED is giving wrong counts

Udayarumilli_DBCCSP_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.


  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.


Run DBCC UPDATEUSAGE on entire database:

Run DBCC UPDATEUSAGE on a specific Table:

Run DBCC UPDATEUSAGE on a specific index:


  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.

Posted in SQL Development | Tagged , , , , , , , , | 1 Comment

Leave a Reply

1 Comment threads
0 Thread replies
Most reacted comment
Hottest comment thread
0 Comment authors
Script to get row count for all tables in a SQL Server database Recent comment authors
newest oldest most voted
Notify of

[…] Here you will find more details and resolution. […]