Post reply

DB in ''Suspect'' state

  • 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!

  • May 30, 2006 at 4:01 am

    #640629

    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

  • May 30, 2006 at 7:44 am

    #640678

    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

  • May 30, 2006 at 7:59 am

    #640685

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

  • May 30, 2006 at 8:50 am

    #640708

    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...

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

    Thanks,

    Sreejith G

     

     

     

  • May 31, 2006 at 9:56 pm

    #641094

    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!

     

  • June 1, 2006 at 2:30 am

    #641109

    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

  • June 1, 2006 at 7:06 am

    #641158

    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

  • June 2, 2006 at 7:35 pm

    #641542

    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!

  • June 7, 2006 at 2:25 am

    #642204

    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

  • June 8, 2006 at 5:15 am

    #642470

    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!

     

  • June 9, 2006 at 2:12 am

    #642717

    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

  • June 9, 2006 at 8:35 am

    #642819

    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

  • June 12, 2006 at 3:45 am

    #643025

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

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

     

  • June 12, 2006 at 8:39 am

    #643135

    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

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

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