Invalid Row Count in Enterprise Manager
-
I have a .asp that searches thru a SQL2k Win@k database table (emailAddr) deleting invalid email addresses. I use an ADODB recordset RS, I display the count before deletions RS.RecordCount 7195. I delete the 4 bad email adrresses that I found and then do a select count(*) emailCount from emailAddr it returns me the correct # of rows, 7191. All is ok so far. I also to the same query in Query Analyzer and I get the correct # of rows, 7191, still all is ok. I open the table in Enterprise Manager with a query, select count(*) from emailAddr and once again I get the correct # of rows 7191. But now here is the problem, when I double click the table name emailAddr, in enterprise manager it tells me there are 7194 rows in the table, what gives?? I have done this at least 10 times with the same result.
So I say to myself now that I know the id of the people with invalid email addresses, I will reload the table with the 7195 original rows and delete them, not from my .asp, but rather using Query Analyzer. Lo and behold, when I do it this way and double click the table name emailAddr, in enterprise manager it tells me correctly that there are 7191 rows in the table, what gives??
Any help would be greatly appreciated. Thanks.
-
This sometimes happens because the statistics aren't updated.
Check the BOL for sp_updatestats.
-SQLBill
-
You should be able to fix this with "DBCC UPDATEUSAGE('tablename') WITH COUNT_ROWS.
This will fix incorrect values in sysindexes, and the sp_spaceused statement will then return the correct number.
-Dan -
Thanks to both Dan and SQLBill.
SQLBill I tried sp_updatestats, it did not work I even did it with the 'resample' param it still did not work. Perhaps I did something wrong, but thanks again for replying.
Dan, the DBCC UPDATEUSAGE('emailAddr') WITH COUNT_ROWS gave me an error that emailAddr was not a valid DB name. When I put the DB name as a parameter............It worked great!! Thanks, I thought I was going crazy!!
Ok, maybe I am going crazy, because now it seems that although the above command worked the first time, now I must run the command any time I insert or delete from any table in the database. Is something corrupt?
Charger.
-
Enterprise manager uses the rowcount from sysindexes, which is only updated when statistics are updated, not necessarily whenever an insert/delete occurs. For this reason, you should never rely on the rowcount given by Enterprise Manager to be exact. If you need an exact row count, use select count(*).
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply