August 27, 2004 at 10:29 am
On a production system running SQL 7.0 (SP4) the following query ends with error Msg 601, Level 12, State 3, Line 1
Could not continue scan with NOLOCK due to data movement.
set transaction isolation level read uncommitted
select x01org, x01idejrn, x01idejrns, x01datingm,x01cntperj, x01cntyarf, x01codcurf, x01datjrn from x01 (index(IX016)) where x01org ='FCCF' and x01desact = 'S' and x01datingm >='20040824'
It seems to corrupt the entire database since we ran DBCC CHECKDB and found errors 2535, 8909, 8929, 8976, 8909 and 8980 in several indexes and tables
I would appreciate any help on this matter.
Regards
August 27, 2004 at 11:24 am
Your database may corrupted prior you run the query. Restored the database backup to anotehr server or new database and run DBCC CHECKDB.
August 27, 2004 at 1:21 pm
Allen sounds right, also check the dump file on SQL Log folder (\Program Files\Microsoft SQL Server\MSSQL\LOG) to check if that query really corrupted the database.
September 1, 2004 at 7:12 am
Thank you for your comments.
Anyway, I found no errors in the LOG before the query was run.
Furthermore, I restored a backup to recover the database and then ran a DBCC CHECKDB with no errors. I thought it was OK, but the client ran a batch and a couple of queries (very similar to the one described above). Those queries failed with error 601 !!. So I ran DBCC CHECKDB and found the database was corrupt again.
So, since we didn't know whether the problem was the batch or the queries, we follow this procedure:
- Restore the backup
- Run DBCC CHECKDB
- Run the batch
- Run DBCC CHECKDB
- Take a backup of the database
- Run the queries
- Run DBCC CHECKDB
Surprisingly, we found no errors in any of these steps. So, the database is now online but we still haven't found the cause of that database corruption
Do you have any comments ?
Regards,
September 1, 2004 at 7:12 am
Thank you for your comments.
Anyway, I found no errors in the LOG before the query was run.
Furthermore, I restored a backup to recover the database and then ran a DBCC CHECKDB with no errors. I thought it was OK, but the client ran a batch and a couple of queries (very similar to the one described above). Those queries failed with error 601 !!. So I ran DBCC CHECKDB and found the database was corrupt again.
So, since we didn't know whether the problem was the batch or the queries, we follow this procedure:
- Restore the backup
- Run DBCC CHECKDB
- Run the batch
- Run DBCC CHECKDB
- Take a backup of the database
- Run the queries
- Run DBCC CHECKDB
Surprisingly, we found no errors in any of these steps. So, the database is now online but we still haven't found the cause of that database corruption
Do you have any comments ?
Regards,
September 1, 2004 at 7:12 am
Thank you for your comments.
Anyway, I found no errors in the LOG before the query was run.
Furthermore, I restored a backup to recover the database and then ran a DBCC CHECKDB with no errors. I thought it was OK, but the client ran a batch and a couple of queries (very similar to the one described above). Those queries failed with error 601 !!. So I ran DBCC CHECKDB and found the database was corrupt again.
So, since we didn't know whether the problem was the batch or the queries, we follow this procedure:
- Restore the backup
- Run DBCC CHECKDB
- Run the batch
- Run DBCC CHECKDB
- Take a backup of the database
- Run the queries
- Run DBCC CHECKDB
Surprisingly, we found no errors in any of these steps. So, the database is now online but we still haven't found the cause of that database corruption
Do you have any comments ?
Regards,
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply