Inaccurate row count with MSSQL
Microsoft SQL Server does a lot of processes in the background. Sometimes, actually quite often on very heavy workload, the background processes run behind schedule to a point where the background operation is not done. Updating index statistics and row counts are some of these background tasks. Microsoft provides the dbcc updateusage to updates its own stats.
dbcc updateusage reports and corrects pages and row count inaccuracies in the various catalog views. This often leads to incorrect data from sp_spaceused system stored procedure.
Applies to:
- Microsoft SQL Server 2000
- Microsoft SQL Server 2005
- Microsoft SQL Server 2008
dbcc updateusage
USE sql911;
go
DBCC updateusage (0) WITH count_rows;
go
go
DBCC updateusage (0) WITH count_rows;
go
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
- If nothing is returned then the usage/indexes statistics are up-to-date.
- Line 3: dbcc updateusage (0) updates the current database.
- Line 3: with count_rows means that the row count column is also updated.
- You need to have admin privileges to the database.
- I suggest that you run this process: dbcc updateusage at least once a work, especially on heavy loaded databases.