May 3, 2004 at 11:37 am
I have an unusual situation. I have a table x which when checked in the Enterprise Manager GUI under properties says that there are 50,560,664 records. A select Count in the query window gives a count of 78,003 records. sp_spaceused gives the 50 million answer, but after asking for all the rows in the EM and then changing the query to select count returns 78,003. A DTS package will export 78,003.
I have reindexed, updated statistics and updated usage, which showed a dramatic change in the data pages. I have disconnected and refreshed and I've had other people go in and look from other machines.
This is not causing any problems as the actual record count is correct. A check of other tables in this database does not show any problems. I've googled it, but it is hard to find specific information. I was just wondering if anyone had seen this before or had any ideas of what else to try. Bouncing the server is not an option for this type of problem and I can probably wait for it's next downtime, but I would rather not.
Thanks in advance
May 3, 2004 at 12:56 pm
Have you tried updateusage with COUNT_ROWS option?
May 3, 2004 at 1:33 pm
The EM count comes from teh index, usually the indexid = 1, so I'm not surprised there. I'd drop and recreate the index if you can. If the above doesn't work as well.
May 3, 2004 at 2:34 pm
The with COUNT_ROW Worked.
DBCC UPDATEUSAGE: sysindexes row updated for table 'XCODE' (index ID 0):
DATA pages: Changed from (4982) to (542) pages.
USED pages: Changed from (1836) to (971) pages.
RSVD pages: Changed from (2931) to (1171) pages.
ROWS count: Changed from (51220109) to (78004) rows.
Thanks.
February 21, 2005 at 6:49 am
Thanks for posting this question and answer! I have been struggling with this as well. DBCC UPDATEUSAGE ('MYDB') WITH COUNT_ROWS
works for me too!
February 21, 2005 at 4:04 pm
yep...use UPDATEUSAGE
"Keep Your Stick On the Ice" ..Red Green
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply