Suspect Status Causes

Question

Post reply

Suspect Status Causes

Jeff Mort

SSC Journeyman

Points: 84
More actions
May 21, 2004 at 3:34 pm

#85588

I am trying to find other causes for a database coming up with the Suspect status on sql 2000. The only reasons that bol list are missing files or lack of disk space. Neither condition is apparent; both mdf & ldf files did not move and there are several gb of space on the drive. Another cause appears to be from a corrupt database - that seems unlikely as the database was brought back online from suspect successfully using sp_resetstatus & dbrecover. It has been in production again for a week without any further incident.
The only other cause I have found is from a Brian Knight's posting suggesting a 3rd party backup software or defrag software could do that. We use Tivoli Storage Manager but it runs late at night and the suspect status occurred in the early evening.
The exact error that occurred at the time of the db going suspect is as detailed in the event viewer application log on our NT 4.0 server -

17053 :
D:\mssql\data\Clinical Engineering_Data.MDF: Operating system error 1224(The requested operation cannot be performed on a file with a user mapped section open.) encountered.

Is the error suggesting the db application caused the issue? Or could a defrag have been attempted by a sys admin user without leaving any tracks and trigger the suspect status? As dba here I have to contend with a networking staff who sometimes appear to make 'assumptions' about issue solutions.

Or are there other causes of Suspect that I have been unable to come up with?

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

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

Darrell Zink SSC Enthusiast Points: 187 More actions · Answer 1

Over the last 10 years, I've had databases marked suspect for a variety of reasons:

- Memory access violations

- Corrupt data

- Corrupt indexes

- Variety of allocation errors

- Missing files

- Others

I've never heard of a database being marked suspect for lack of disk space.

Just because the database came back after running sp_resetstatus does not mean that there was no corruption in the database. You must run checkdb and, perhaps, other integrity checks before assuming that the database is OK.

Jeff Mort SSC Journeyman Points: 84 More actions · Answer 2

Thanks for the other possible causes.

I should have mentioned that I ran both checkdb & checkalloc without either coming back with any errors. And also that since the db size averages 140MB that there is virtually no chance of the drive running out of space.

I still wonder if someone could have tried to run the nt defrag on the drive and that triggered the suspect status?

I also wonder if the o/s 'system error 1224(The requested operation cannot be performed on a file with a user mapped section open.) encountered' could have been caused by the db application? The vendor is unaware of any issue. Or is the error 1224 suggesting that sql server itself triggered the error and then the suspect status? Or did the suspect status occur first then trigger the error 1224? The sql error logs only contain entries after the db came back online after the suspect status.