Post reply

SUSPECT database

  • July 30, 2007 at 6:10 am

    #179946

     

    Hi,

    My laptop hanged as I was running a large table update and when I restarted my database was marked as 'SUSPECT'.

    Running sp_resetstatus @dbname = "mydb" says that the flag has allready been reset but does not make any diffference.

    When I try

    SP_CONFIGURE 'allow updates', 1

    RECONFIGURE WITH OVERRIDE

    GO

    UPDATE master..sysdatabases set status = -32768 WHERE name = 'mydb'

    GO

    SP_CONFIGURE 'allow updates', 0

    RECONFIGURE WITH OVERRIDE

    I get the error message

    Update or insert of view or function 'master..sysdatabases' failed because it contains a derived or constant field.

    Is there any other alternative to get into emergency mode to recover the data in the database?

     

    Rgds and Thx

    Christo

  • July 30, 2007 at 1:37 pm

    #723403

    You did not mention how you restarted your server. If you turned off its power, or do something else without allowing your updates to rollback, your log file is damaged.

    My suggestion is:

    1. detach your database;

    2. re-attach your data file, and create a new log file at the same time (your log file is most likely damaged if you restarting your services not gracefully).

    Of course, your imcomplete transactions will be lost in the above procedures.

    Hopefule, the above is helpful.

  • July 31, 2007 at 1:20 pm

    #723702

    BTW, your update to the sysdatabases table did not work because it is not a table in SQL 2005 anymore.

    It is a view based on the sys.databases table that is made to look like the old table for backward compatibility.

    If you do an sp_helptext sysdatabases you will see that the status field is now a calculated field in the view.

    You need to update the underlying table, not the view.

  • August 1, 2007 at 8:10 am

    #723874

    I guess the issue was first open from SQL Server 7.0, but SQL 2005 for emergency mode you have to execute:

    alter database mydb

    set emergency

    go

    You don't need to restart SQL Server Service for this change.

    To rollback this you exeecute

    alter database mydb

    set online

    go

     

     

  • September 12, 2007 at 6:42 pm

    #733333

    This is terrible advice. Most suspect databases WILL NOT reattach successfully.

    Cristo - did the database/log run out of space? Why did it go suspect in the first place? You may be able to just expand the log or data files and then re-start recovery to get it out of suspect mode.

    Paul Randal
    CEO, SQLskills.com: Check out SQLskills online training!
    Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
    SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
    Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

  • February 19, 2009 at 10:31 pm

    #947317

    Paul Randal (9/12/2007)

    This is terrible advice. Most suspect databases WILL NOT reattach successfully.

    Cristo - did the database/log run out of space? Why did it go suspect in the first place? You may be able to just expand the log or data files and then re-start recovery to get it out of suspect mode.

    hi i am nishantha

    my database suspect how can i recovary

    thanks

  • February 20, 2009 at 2:19 am

    #947365

    nishantha0 (2/19/2009)

    hi i am nishantha

    my database suspect how can i recovary

    thanks

    Please post your problem in a new thread and explain, in detail, what happened and what the problem is. Check the SQL error log for related errors.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 22, 2009 at 4:26 am

    #964089

    HI nishantha,

    you run the this screep IN Query Analyzer ,

    USE Master

    GO

    -- Determine the original database status

    SELECT [Name], DBID, Status

    FROM master.dbo.sysdatabases

    GO

    -- Enable system changes

    sp_configure 'allow updates',1

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    -- Update the database status

    UPDATE master.dbo.sysdatabases

    SET Status = 24

    WHERE [Name] = 'DATABASE NAME'

    GO

    -- Disable system changes

    sp_configure 'allow updates',0

    GO

    RECONFIGURE WITH OVERRIDE

    GO

    -- Determine the final database status

    SELECT [Name], DBID, Status

    FROM master.dbo.sysdatabases

    GO

    kaushalendra Singh

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

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