June 16, 2006 at 9:12 am
Good morning,
SQL 2000 SP4, Windows2003
Strange issue appeared this morning that I haven't encountered before.
Table1 has 3591 rows in it when I run 'select count(*) from table1'.
When I return the entire results set in qa, I scroll down to row 3591.
BUT, When I use SQL Enterprise Manager, right click on table1, select properties and look at the row count, I see 3583.
Ran dbcc checkdb, dbcc newalloc, sp_updatestats, and dbcc checkcatalog.
Not sure why this happens.
Have a nice day.
BPH
BPH
June 16, 2006 at 11:17 am
Run a DBCC UPDATEUSAGE on that table and then do your record count using both methods. Let us know what happens.
Mark
June 16, 2006 at 11:27 am
Hi Mark,
dbcc updateusage didn't resolve the issue.
Thanks, Brian
BPH
June 16, 2006 at 11:50 am
Next thing to try is a DBCC DBREINDEX on that table and see what happens.
Mark
June 16, 2006 at 12:04 pm
Negative.
BPH
June 16, 2006 at 12:11 pm
What does the "rowcnt" column in sysindexes show for that table?
Mark
June 16, 2006 at 12:17 pm
One last thing, when you ran the UPDATEUSAGE command, did you do a refresh on the table in EM before looking at the properties?
Mark
June 16, 2006 at 12:49 pm
Yes, I even disconnected and reconnected. .
BPH
June 16, 2006 at 12:58 pm
Mark,
rowcount sysindexes says 3583.
Thanks.
BPH
June 16, 2006 at 1:05 pm
From what I know, the GUI uses sysindexes to report the row count of a table, that's why you are getting a different number. However, what is the mystery to me is why the DBCC UPDATEUSAGE isn't changing the rowcnt column to the number of actual rows in the table. Did you try using the WITH COUNT_ROWS option in the UPDATEUSAGE statement. From BOL:
COUNT_ROWS
Specifies that the rows column of sysindexes is updated with the current count of the number of rows in the table or view. This applies only to sysindexes rows that have an indid of 0 or 1. This option can affect performance on large tables and indexed views.
Mark
June 16, 2006 at 1:20 pm
Mark,
Awesome job!! With Count_Rows did the trick.
Thanks for all your help. enjoy the weekend.
Brian
BPH
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply