By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,149 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Scary Results

P: n/a
We're currently getting the following behaviour on a SQL2K production
database at the moment:

select count(*) from Tab_1
-------------
3804049

(1 row(s) affected)

select count(*) from Tab_1 where datearchived is null
-----------
3753144

(1 row(s) affected)

select count(*) from Tab_1 where (batch is not null or batch != ') and
datearchived is null
-----------
3774095

(1 row(s) affected)

In other words, adding a further restriction returns MORE records.

To make it scarier, this is NOT consistent; sometimes we get the above
behaviour, and then for a few minutes the second query will return
fewer records, before reverting back to the same incorrect results
again:

-----------
3804049

(1 row(s) affected)

-----------
3753145

(1 row(s) affected)

-----------
3751486

(1 row(s) affected)

The incorrect results are the norm, though.

I've dropped / recreated all indexes in case one of them was corrupt -
made no difference.
We've DTS'd the table to another server, and failed to reproduce the
problem.
We've BCP'd the data to another server and failed to reproduce the
problem.
We're currently recreating a copy database by attaching a copy of the
data file taken last night from the production db, in the hope that a
binary copy will enable us to reproduce it outside the production
server. If we can do that, then we can play around with the data a bit
more.

We're 99% sure the problem lies in the NULL values being held in the
datearchived column, but we aren't sure exactly what is wrong with
them.

Has anyone seen anything remotely like this before? Anyone got any
further ideas we could borrow?

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Phil,

query 3 is an invalid query. It tries to compare column batch to an
unclosed string. As it is written now, this unclosed string includes the
text "and datearchived is null".

Gert-Jan
Phil wrote:

We're currently getting the following behaviour on a SQL2K production
database at the moment:

select count(*) from Tab_1
-------------
3804049

(1 row(s) affected)

select count(*) from Tab_1 where datearchived is null
-----------
3753144

(1 row(s) affected)

select count(*) from Tab_1 where (batch is not null or batch != ') and
datearchived is null
-----------
3774095

(1 row(s) affected)

In other words, adding a further restriction returns MORE records.

To make it scarier, this is NOT consistent; sometimes we get the above
behaviour, and then for a few minutes the second query will return
fewer records, before reverting back to the same incorrect results
again:

-----------
3804049

(1 row(s) affected)

-----------
3753145

(1 row(s) affected)

-----------
3751486

(1 row(s) affected)

The incorrect results are the norm, though.

I've dropped / recreated all indexes in case one of them was corrupt -
made no difference.
We've DTS'd the table to another server, and failed to reproduce the
problem.
We've BCP'd the data to another server and failed to reproduce the
problem.
We're currently recreating a copy database by attaching a copy of the
data file taken last night from the production db, in the hope that a
binary copy will enable us to reproduce it outside the production
server. If we can do that, then we can play around with the data a bit
more.

We're 99% sure the problem lies in the NULL values being held in the
datearchived column, but we aren't sure exactly what is wrong with
them.

Has anyone seen anything remotely like this before? Anyone got any
further ideas we could borrow?

Jul 23 '05 #2

P: n/a
You're right. Not sure how that happened, but it is just a cut and
paste error. Please assume that this is an empty string '' -
everything else is the same.

Jul 23 '05 #3

P: n/a
Check out MSKB 814509
<http://support.microsoft.com/default.aspx?scid=kb;en-us;814509> to see if
it applies. If so, another workaround is to specify a MAXDOP 1 hint.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Phil" <ph********@btopenworld.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
We're currently getting the following behaviour on a SQL2K production
database at the moment:

select count(*) from Tab_1
-------------
3804049

(1 row(s) affected)

select count(*) from Tab_1 where datearchived is null
-----------
3753144

(1 row(s) affected)

select count(*) from Tab_1 where (batch is not null or batch != ') and
datearchived is null
-----------
3774095

(1 row(s) affected)

In other words, adding a further restriction returns MORE records.

To make it scarier, this is NOT consistent; sometimes we get the above
behaviour, and then for a few minutes the second query will return
fewer records, before reverting back to the same incorrect results
again:

-----------
3804049

(1 row(s) affected)

-----------
3753145

(1 row(s) affected)

-----------
3751486

(1 row(s) affected)

The incorrect results are the norm, though.

I've dropped / recreated all indexes in case one of them was corrupt -
made no difference.
We've DTS'd the table to another server, and failed to reproduce the
problem.
We've BCP'd the data to another server and failed to reproduce the
problem.
We're currently recreating a copy database by attaching a copy of the
data file taken last night from the production db, in the hope that a
binary copy will enable us to reproduce it outside the production
server. If we can do that, then we can play around with the data a bit
more.

We're 99% sure the problem lies in the NULL values being held in the
datearchived column, but we aren't sure exactly what is wrong with
them.

Has anyone seen anything remotely like this before? Anyone got any
further ideas we could borrow?

Jul 23 '05 #4

P: n/a
Dan - thanks for that. Looks like it was spot on. We'd already
managed to get around the problem by dropping a covered index (which
changed the query plan to a clustered index scan, which for some reason
seems to have got rid of the problem).

Longer-term, I prefer your MAXDOP suggestion for this, since that
would enable us to reinstate the index - we've tested this and it does
work.

Thanks again.

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.