Post reply

SUSPECT DATABASES

  • April 18, 2008 at 2:14 am

    #71555

    Please help!

    Critical updates were loaded on SQL2k server last night and now databases are showing as Suspect. have run the following scripts, restarted SQL Server but the Databases are still suspect. Any suggestions? Many thanks.

    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] = 'YourDatabaseName'

    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

  • April 18, 2008 at 2:58 am

    #804156

    - keep your head cool ( if you don't, no one else will do it for you !)

    - have a look a sqlserver errorlog file and search for anomalies

    - take a log-backup if you can so you may be able to perform a point in time restore.

    - run DBCC CHECKDB for your suspect db (read books online for details !! )

    and see what kind of messages it brings..

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • April 18, 2008 at 3:19 am

    #804167

    What's the latest backup you have for those databases?

    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

  • April 18, 2008 at 3:40 am

    #804176

    Hi,

    Critical updates are currently being removed, so am unable to access Log file at present.

    Have run DBCC CheckDb and get the following

    Server: Msg 945, Level 14, State 2, Line 1

    Database '31039M' cannot be opened due to inaccessible files or insufficient memory or disk space.

    See the SQL Server errorlog for details.

    We have loads of disk space and I can see the mdf and ldf files, where they should be.

    Have Back ups from last night, which is some comfort.

    Thanks

    Colin

  • April 18, 2008 at 3:45 am

    #804182

    Any idea what the following values from sysdatabases are - they are not mentioned in BOL and Google is not being much help, at prtesent.

    STATUS1 STATUS2

    DBase1 24 1090519040

    DBase2 1073741848 1090519040

    DBase3 1077936144 1090519040

    Dbase4 1073741848 1090519040

  • April 18, 2008 at 4:01 am

    #804190

    When you can read the SQL error log, please post any entries that look strange, have error severities above 19 or refer to the suspect databases.

    It's important to know why the databases are suspect before fixing them

    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

  • April 18, 2008 at 4:15 am

    #804198

    Hi,

    Error Log stated that it had problems access the Drive holding the LDF files or the LDF files themselves.

    Investigations showed that the AD user which runs SQL Server had been removed from the Administrator Group. Restoring this permission immediately brought the databases back up.

    Thank you all for your comments and support - especially about keeping a cool head - WISE WORDS!

    Thanks

    colin

  • April 18, 2008 at 4:36 am

    #804211

    Great to hear. Nice work.

    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

  • April 18, 2008 at 5:05 am

    #804230

    I'm glad you found the issue and were able to fix it.

    You could also have granted full control or the service account to the folder that contains the logfiles.

    But that may have caused some issues as well if e.g. sqlagent jobs reading/writing local files, ...

    If everything is back up and running, it's time to get rid of the ice on the head, so lose the stress 😉

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

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

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