Post reply

Different Row Count between Enterprise Manger and Query Analyser

  • May 3, 2004 at 11:37 am

    #85313

    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

    #505031

    Have you tried updateusage with COUNT_ROWS option?

  • May 3, 2004 at 1:33 pm

    #505034

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 3, 2004 at 2:34 pm

    #505041

    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

    #543306

    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

    #543380

    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