Show Suspect but not suspect

admindba

One of my database (name XYZ) shows suspect status in EM but when i try to dig further by running below query i get only "OK" ( see query)

SELECT [name],status, case status when (status & 32) then 'Loading'
when (status & 32) then 'Loading'
when (status & 64) then 'Pre Recovery'
when (status & 128) then 'Recovering'
when (status & 256) then 'Not recoverd'
when (status & 512) then ' Offline'
when (status & 1024) then ' Single user'
when (status & 1024) then ' Read only'
when (status & 32768) then ' Emergency Mode' else 'OK' end as status
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('distribution', 'tempdb', 'model','Pubs','Northwind')

also i run this

select * from sysdatabases where databaseproperty(name,N'IsSuspect') <> 1

and here also i get all the database including "XYZ"...i guess if "XYZ" is suspect the resultset should not be including "XYZ"

Why if the EM shows suspect status FOR "XYZ" DATABASE it should come up when i check status column in sysdatabases table

Dhericean

First off you do not say which version of SQL Server you are using. Whilst it may seem petty direct access to the sysdatabases is not recommended in 2005 (you should use sys.databases). sys.databases includes 2 columns state and state_desc which can directly report suspect status (values 4 and 'SUSPECT' respectively).

On the Status field in sysdatabases - I am not sure what the status of a Suspect database should be on that basis. Suspect may well be one of the undocumented flags in the status field, also the mode field is involved (as sp_resetstatus affects both).

What is returned when you query:

SELECT DATABASEPROPERTY(N'XYZ', N'IsSuspect')

Although again the recommendation seems to be to use DATABASEPROPERTYEX, so you could also try:

SELECT DATABASEPROPERTYEX(N'XYZ', N'Status')

What do the logs files tell you about the details of why the database has been marked as suspect

If it is one of the files you could try:

SELECT * FROM sys.master_files WHERE (STATE = 4)

This will tell you any files (across all databases) which have a suspect state (unfortunately you cannot use sys.database_files when the database is offline).

Generally if you have a backup (or can rebuild) the suspect database then you are better doing that. As you need to check this database thoroughly even if you can bring it online again.

admindba

Thanks for the reply....sorry i could not get back early

first of all

I am using sql server 2000...

when i saw error log i found it was "file access error OS error 32 " SQL server could not access the .mdf file...but still my question is same...In EM it shows suspect database but when i try to query and see the status it does not give me as suspect status.In my feeling it is misleading as it says suspect but actually it is not because when i restrted the server the database recoverd properly and online now...

any idea why it show suspect but when u query sysdatabase "status" column and get nothing as suspect database.

thanks in advance