Sql database flagged as suspect
Results 1 to 5 of 5

Thread: Sql database flagged as suspect

  1. #1
    Join Date
    Apr 2016
    Posts
    2

    Sql database flagged as suspect

    We run SQL on a Win2K server. The database for an OOB product began to be flagged as 'suspect' a few days ago. The first couple of times we were able to stop/start MSSQL and the database would come back up. Today it wouldn't. In an attempt to repair the problem, I detatched the db. When I attempt to re-attach, I was getting the following message

    error 5101: Device activation error. The physical file name 'C:\%path to db%\db_log.LDF ' may be incorrect. Then I get db Attach failed.

    I resolved this by creating a new DB of the same name, stopping SQL and replacing the MDF with the corrupt file. I'm now back to 'suspect' on the DB in Enterprise Manager.

    When I run DBCC CHECKDB ('dbname', REPAIR_REBUILD) I get the response below:

    Server: Msg 945, Level 14, State 2, Line 1
    Database 'dbname' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

    Any ideas?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Is disk full on the machine? Any disk related error in windows event logs?

  3. #3
    Join Date
    Apr 2016
    Posts
    2
    If you have no reliable backups, I would create a new database and start copying tables and any other objects as fast as you can, before it dies permanently. If the data is inaccessible then can try the tool SQL Server Recovery Toolbox. It is possible you will be able to help. http://www.oemailrecovery.com/sql_repair.html

    Also try this. This probably won't work, but it forces the suspect flag off.
    You will first need to turn on update for systables. So run his

    USE Master
    Go
    EXEC sp_configure 'allow updates' , 1
    Go
    Reconfigure
    Go

    Then run the following code:

    update sysdatabases
    set status = status & ~256
    where name = 'MySuspectDatabase'


    If that doesn't fix it. You will want to start copying all
    the objects to a new db as ACPerkins has suggested. You will
    want to place the db in emergency mode. First so run this:

    update sysdatabases
    set status = status | -32768
    where name = 'MioSuspectDatabase'

    Then use dts, Select into's and whatever else to get the objects from
    the suspect DB to a new one. Dont forget to undo the allow updates option:

    EXEC sp_configure 'allow updates' , 1
    Go
    Reconfigure
    Go

  4. #4
    Join Date
    Apr 2016
    Posts
    2
    May be this article will give you more information.
    http://www.techrepublic.com/forums/q...-is-corrupted/

  5. #5
    Join Date
    Apr 2016
    Posts
    2
    Thank you guys for your feedback and help. Sorry, that long time did not respond. This is because the issue was solved and I forgot to tell.) Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •