Post reply

Query Returning Different Incorrect Result Set Each Time

  • January 23, 2007 at 8:21 am

    #115419

    Hi all

    Had a power failure at one of our hosting sites on Sunday (down for 1.5hrs) and since then one of our databases performance has steadily degraded.

    The database participates in replication, is a subscriber in transactional replication.

    Here is the query being executed (certain names have been changed):

    select u.user_id,login_name,first_name,last_name,email,country

    from table1 u inner join table2 f on f.user_id = u.user_id

    where f.allowed_to_login = 1

    and u.email not in (select emailaddress from tablec)

    and u.country not in ('USA', 'United States')

    and u.email is not null

    and u.email <> ''

    If I run count(*) or just select the user_id column, the query runs for a decent amount of time and returns 89000 rows.

    When executing the full statement the query will run for different amounts of time and then complete successfully but never return the 89000. Everytime the query is executed the return rowcount is different.

    Other times will run for an hour and when cancelling will display 10 000 rows for example.

    I ran DBCC DBREINDEX and DBCC INDEXDEFRAG with no luck.

    I've even dropped all the indexes on the 3 tables, ran sp_updatestats and dbcc updateusage and nothing has changed.

    If I run the query in EM, returns all 89000, takes awhile but returns them.

    I can run this query on another database on the same server (same structure but less data) and returns the correct rowcount. Execute on another server/database which also has in excess of 80 000 records and it executes fine each time.

    Network Admin can't find any I/O issues or bandwith/connection issues.

    I'm extremely concerned about what other data it might not be displaying.

    Any suggestions?

    Thanks

    John


    John Mac Pherson
    Database Administrator
    Optimal Information Systems Pty (Ltd)
    jmacpherson@ois.co.za

  • January 23, 2007 at 8:57 am

    #684966

    We had a similar problem and had to apply a hotfix:

    http://support.microsoft.com/kb/814509/

    Russel Loski, MCSE Business Intelligence, Data Platform

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply