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.

  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:

Run DBCC UPDATEUSAGE on entire database:

Run DBCC UPDATEUSAGE on a specific Table:

Run DBCC UPDATEUSAGE on a specific index:

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.

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

Leave a Reply

1 Comment on "SP_SPACEUSED is giving wrong counts"

Notify of
avatar
Sort by:   newest | oldest | most voted
trackback

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

wpDiscuz