Non suspect databases showing up as Suspect

Question

Non suspect databases showing up as Suspect

RSingh-135921

Default port

Points: 1438
More actions
August 1, 2005 at 8:21 am

#88320

Hi,
I have a strange problem at my hand.
Some of my databases are showing up as Suspect in EM but when i run
the following query:
Select databaseproperty('dbname','IsSuspect') it gives the result 0
which means db is not in suspect mode.
Also the status column in master..sysdatabases for the suspect databases has value 1073741832.
Please help.
Regards,
Ravinder

Viewing 7 posts - 1 through 6 (of 6 total)

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

Steve Jones - SSC Editor SSC Guru Points: 728164 More actions · Answer 1

I think you have a suspect database if it is flagged in master, regarldless of the databaseprooperty return. Check your error log for issues.

Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com

RSingh-135921 Default port Points: 1438 More actions · Answer 2

Steve,

Thanks for the reply.

But when i tried to reset its status using sp_resetstatus I got the following message:

No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

But still the databases are showing up as Suspect in EM and not accessible thru QA or EM. This is strange.

Ravinder

raj-247939 SSCertifiable Points: 5999 More actions · Answer 3

HI,

Pls put ur database online

USE master

GO

ALTER DATABASE pubs

set online

GO

hope this helps

from

killer

peterhe SSChampion Points: 11469 More actions · Answer 4

the status 1073741832 means the database was cleanly shut down. When you took a database off line, the status is EM is 'Offline'. After you restart SQL server, the status of the offline database will change to 'Suspect\Offline'.

So if your DB status now is 'Suspect\Offline', Raj's recommendation should work.

If the status is just 'Suspect', the database log may be corrupt. You can confirm it with the errorlog and try to rebuild the database log by the following script. You will lose the transactions not recovered.

use master

go

Sp_configure allow, 1

Go

Reconfigure with override

Go

Update sysdatabases set status = 32768 where name = 'YourDBName'

go

--dbcc rebuild_log(Db_Name, new_log_file)

dbcc rebuild_log('YourDBName','d:\Microsoft SQL Server\MSSQL\data\YourDB_Newlog.ldf')

M.S. Reddy SSC Veteran Points: 282 More actions · Answer 5

One common reason for this behavior is that SQL Server could not find / access the Transaction log file of the database. check out your ErrorLog for errors (something like "Device activation error. The physical file name 'D:\MSSQL\data\pubs_log.ldf' may be incorrect.").

The IsSuspect returns true if the TLog file is accessible, but corrupt or data file is corrupt. If the files are not accessible, EM shows suspect, but the Status in sysdatabases remains unchanged at old value (Clean Shut down in your case)

Hope this helps

Thanks

Sekhara Reddy

RSingh-135921 Default port Points: 1438 More actions · Answer 6

Thanks to everyone who responded to this topic.

Reddy,

You got it right. Our IT Team did some changes to our storage server which resulted in some of the data files being inaccessible. The problem was solved with their help.

Thanks once again to everyone.

Regards,

Ravinder