Table rowcount in properties is incorrect

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

lucycard - 08 May 2006 16:05 GMT

I have DTS jobs that delete and copy a table from one server to another.

I check the rowcount in the receiving server's table properties by right
clicking the table and selecting properties and it has not updated properly
in several weeks.

I ran the EXEC sp_updatestats and the record count is still incorrect.

When I do a select count(*) from tablename the count is correct.

Is there a way to correct the table property's record count?

Reply to this Message

Jeje - 09 May 2006 04:12 GMT

the BOL says
"In SQL Server 2005, sp_updatestats updates only those statistics that
require updating based on the rowmodctr information in the sys.sysindexes
compatibility view; therefore, preventing unnecessary updates of unchanged
items."

So if you use SQL 2005, maybe the sp_updatestats don't really update the
stats.

try to use the update statistics command

sp_msforeachtable 'update statistics ? with fullscan'

also, you can defragment your indexes, the alter index command (in SQL 2005)
could defrag AND update the statistics at the same time.

(a dbcc command exists in SQL 2000 too, but I don't remember its name)

does this help you?

>I have DTS jobs that delete and copy a table from one server to another.
>
[quoted text clipped - 8 lines]
>
> Is there a way to correct the table property's record count?

Reply to this Message

lucycard - 09 May 2006 21:06 GMT

Thanks, I'll try the sp_msforeachtable 'update statistics ? with fullscan' on
SQL2000 to get the rowcount updated.

> the BOL says
> "In SQL Server 2005, sp_updatestats updates only those statistics that
[quoted text clipped - 28 lines]
> >
> > Is there a way to correct the table property's record count?

Reply to this Message

lucycard - 11 May 2006 16:51 GMT

I did try executing the statement in QA and went back into EM and found the
table's rowcount on the properties display still has not updated.

> Thanks, I'll try the sp_msforeachtable 'update statistics ? with fullscan' on
> SQL2000 to get the rowcount updated.
[quoted text clipped - 31 lines]
> > >
> > > Is there a way to correct the table property's record count?

Reply to this Message

Alejandro Mesa - 21 May 2006 02:09 GMT

lucycard,

I am not sure, but I think EM get that value from the system table
[sysindexes], and sometimes this value get out of sync.

How to fix this?

dbcc updateusage (0, 'table_name') WITH COUNT_ROWS

or

exec sp_spaceused @objname = 'table_name', @updateusage = 'TRUE'

AMB

> I did try executing the statement in QA and went back into EM and found the
> table's rowcount on the properties display still has not updated.
[quoted text clipped - 34 lines]
> > > >
> > > > Is there a way to correct the table property's record count?

Reply to this Message

Terry - 24 May 2006 21:08 GMT

I have a simple question about rowmodctr. Will it only update statistics
if this field in > 0?

Reply to this Message

Alejandro Mesa - 04 Jun 2006 17:36 GMT

Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/st
atquery.asp?frame=true

AMB

> I have a simple question about rowmodctr. Will it only update statistics
> if this field in > 0?
>
> *** Sent via Developersdex http://www.developersdex.com ***

Reply to this Message