DB in ''Suspect'' state

Question

DB in ''Suspect'' state

Ballack Cheng Soo How

Old Hand

Points: 362
More actions
May 29, 2006 at 4:48 am

#113142

Hi all,
This DB turns to 'suspect' state after one of the 4-drive RAID 5 found faulty, and since being removed.
What is the proper way in handling this situation. Shall I change the state? or else?

Thanks in advance!

Viewing 15 posts - 1 through 14 (of 14 total)

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

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 1

Hi, other more experienced DBA's may advise otherwise, but my two pence worth. Try DBCC CHECKDB ('database', REPAIR_REBUILD).

Lookup the options available with DBCC CHECKDB. But I do believe generally when a database is in suspect mode, you should restore it from a backup as I believe bits internal to SQL Sever are not correct and hence pages are out of sync. As I say far more experience DBA's could advise you better.

Rgds

Derek

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 2

For bringing db to its normal state you may need to restore the db from latest db backup.

Even if their may be other options, db restore is always recommended.

------------
Prakash Sawant

http://psawant.blogspot.com

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 3

I would agree with Prakash, despite my comments on DBCC CHECKDB. Rgds Derek

~Sreejith G SSCrazy Points: 2311 More actions · Answer 4

Whats the error message errorlog file of Sql Server showing?

Cant you fix the drive which find faulty...

Is it not mirrored? If you can bring back the drive with help of hardware system people then the steps you need to follow is...

Reset the suspect status by executing sp_resetstatus.
Run recovery by executing DBCC DBRECOVER (database).

Thanks,

Sreejith G

Ballack Cheng Soo How Old Hand Points: 362 More actions · Answer 5

Hi everyone out there,

Many thanks for you guys' suggestion.

Now this DB is "disappearing" at all. No way to recover.

Now, I want to restore this db from the latest backup tape (in *.bak format). At first, I tried to create a new db. Whenever I tried to create a new db with the same name as previously, EM won't allow me to proceed with the reason that database is already exists.

I badly need to use the same db name, as I don't want to change few settings in the DSN. What should I do?

Can "drop database" does the magic?

Thanks in advance!

Prakash Sawant SSCrazy Points: 2173 More actions · Answer 6

you can restore the backup on your current db itself or even drop & create db will also work in your case.

------------
Prakash Sawant

http://psawant.blogspot.com

SQLBill SSC Guru Points: 51440 More actions · Answer 7

When you restore a database, normally you DON'T create it first. In your case, you have two options:

1. DROP the database and then do the restore, or

2. RESTORE the database using the option WITH REPLACE. That will force the restore to replace the existing database of the same name.

-SQLBill

Ballack Cheng Soo How Old Hand Points: 362 More actions · Answer 8

Hi everyone out there,

Thanks for the comments. Now, I get the clearer idea.

Suppose I chose to drop, recreate the db, and then restore from the backup. How about the logins? I heard that you have to do something to make sure that the all the logins won't have permission denied issue.

Thanks in advance!

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 9

Hi, this works for me when replacing the database.

-- Get locations of existing mdf and ldf

use <database>

select * from sysfiles

GO

-- Get LOGICAL and PHYSICAL names from a .BAK file.

-- --------------------------------------------------------------------

use master

restore filelistonly from DISK = '\\gtlidbarc\g$\dumps\gtlidoc01c\full\<backupfile>'

-- Restore the Doc01Archive_Level1 database from a .BAK file

-- ---------------------------------------------------------

restore database <database>

from disk = '\\gtlidbarc\g$\dumps\gtlidoc01c\full\<backupfile>'

with move 'Doc01_Data' to 'z:\SQLData\mssql\data\Doc01Archive_Level1_Data.mdf',

move 'Doc01_Log' to 'l:\SQLLog\Doc01Archive_Level1_log.ldf',

REPLACE

Regarding logins, you can resync the logins. If you need help on this, I'll dig up a script I use.

Hope this is of help.

Rgds

Derek

Ballack Cheng Soo How Old Hand Points: 362 More actions · Answer 10

Hi Derek,

Thanks for enlighting me on how to go about when db is in suspect mode. Would rather appreciate if you can show me how to resync the logins.

Thanks a lot!

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 11

Hi, below is the T-SQL I run to resync logins. You need to run it against each user database as required etc. In this case your suspect database. Hope it does the job for you. Rgds Derek.

Use <database>

GO

DECLARE @UserName NVARCHAR(255)

DECLARE orphanuser_cur CURSOR FOR

SELECT UserName = [name]

FROM sysusers

WHERE issqluser = 1 --Is a SQL Server Login

AND sid IS NOT NULL --User must have a valid sid, excludes roles

AND sid <> 0x0 --Guest Account

AND SUSER_SNAME(sid) IS NULL --Sid that doesn't map back to a login

ORDER BY [name]

OPEN orphanuser_cur

FETCH NEXT FROM orphanuser_cur INTO @UserName

WHILE @@fetch_status = 0

BEGIN

PRINT @UserName + ' user name being resynced'

EXEC master.dbo.sp_change_users_login 'Update_one', @UserName, @UserName

FETCH NEXT FROM orphanuser_cur INTO @UserName

END

CLOSE orphanuser_cur

DEALLOCATE orphanuser_cur

GO

mngong SSCertifiable Points: 6167 More actions · Answer 12

There is also a GUI tool tool that could help resync logins (Good if you have just a few logins )

http://www.dbmaint.com/SyncSqlLogins.asp

Mike

Ballack Cheng Soo How Old Hand Points: 362 More actions · Answer 13

Hi Derek, Veteren, and all of you out there,

Many thanks to all of you. I managed to restore the db with all logins.

Derek Wallace Ten Centuries Points: 1079 More actions · Answer 14

Ah your very welcome. I've got lots of help from people on this forum. Even on this thread the tip from veteran was great. Best of luck in the future with SQL Server. Enjoy. Derek