Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2004
    Posts
    7

    Unanswered: database in suspect mode

    Hi

    my database went into suspect mode. i tried to reset the status database by "sp_resetstatus abc". It is giving the message

    "Prior to updating sysdatabases entry for database 'cr2db', mode = 0 and status = 260 (status suspect_bit = 256).
    For row in sysdatabases for database 'cr2db', the status bit 256 was forced off and mode was forced to 0.
    Warning: You must recover this database prior to access."


    please let me if you have any solution to this problem.


    thank you in advance

    ricky

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Before you do ANYTHING, you do have two good backups, right?

    Operative word being should, you should be able to simply stop the SQL Server and restart it. When the server starts up, it puts all of the databases through recovery.

    -PatP

  3. #3
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by Pat Phelan
    Before you do ANYTHING, you do have two good backups, right?

    Operative word being should, you should be able to simply stop the SQL Server and restart it. When the server starts up, it puts all of the databases through recovery.

    -PatP
    I tried to restart the SQL Server. But still it is in suspect mode. I tried to update the status of the database is sysdatabase tables to 0 and then again restarted the sql server. when i check status field in sysdatabases it is showing 256. can anybody tell me why it changing to 256 when i set it to 0.

  4. #4
    Join Date
    Jan 2004
    Location
    Boston
    Posts
    58
    Have you checked SQL errorlog?Could it be any process accessing the data or log files? Is there enough disk space?

  5. #5
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by rding
    Have you checked SQL errorlog?Could it be any process accessing the data or log files? Is there enough disk space?

    I has 8 GB disk space. and i checked the error log, it has an entry

    "Bypassing recovery for database 'abc' because it is marked SUSPECT"

    and another entry is

    "Failed to obtain TransactionDiscpenserInterface: Result Code =0x8004d01b"

    ricky

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ricky503
    I tried to restart the SQL Server. But still it is in suspect mode. I tried to update the status of the database is sysdatabase tables to 0 and then again restarted the sql server. when i check status field in sysdatabases it is showing 256. can anybody tell me why it changing to 256 when i set it to 0.
    Damn...

    I was going to suggest that you dump the suspect database BEFORE you did the rerstart...

    What does DBCC CHECKDB tell you?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    I wwould

    I would recommend you taking backup of log first .

    Check the Article here..

    http://search.microsoft.com/search/r...-us&qu=Q165918


    seems like either you may have run out of disk space or a drive is bad or somthing

  8. #8
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by Brett Kaiser
    Damn...

    I was going to suggest that you dump the suspect database BEFORE you did the rerstart...

    What does DBCC CHECKDB tell you?
    DBCC check has no errors

    "CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'."

  9. #9
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by ricky503
    DBCC check has no errors

    "CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'."
    Hi

    I am using ms sql server 2000, one of my databse went into suspect mode, i cehcked the database. it has no error

    "CHECKDB found 0 allocation errors and 0 consistency errors in database 'master'."

    i try to reset the status. but it didnot worked. I have 8 GB disk space available.


    I have the device files abc.mdf (14 GB) , abc_ind.ndf( .5 GB) and abc_log.ldf (.5 GB). can anyone tell me how to recover by database from these files.

    can i copy these file to different device( "E" having 50GB ) and create a new database ?

    I have to recover this database. please help me out

    thank in advance

    ricky

  10. #10
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Have you followed as per the books online Resetting the Suspect Status topic.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  11. #11
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by Satya
    Have you followed as per the books online Resetting the Suspect Status topic.
    Yes I tried. But it didnot work.

  12. #12
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    How about reattaching the data file. Give a try; Stop SQL and make a copy of ur data-file, attach the data file with another DB name, create a new transaction file by renaming the older one prior to attach.
    Just an idea.

    Howdy!

  13. #13
    Join Date
    Sep 2003
    Location
    KY
    Posts
    146

    check

    check sp_attach_db . If nothing works you may be able to recover major part of it

    I would recommend you doing the backup of log

  14. #14
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    I think this will also fail as the database status is suspect you cannot access it to detach and attach.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  15. #15
    Join Date
    Apr 2004
    Posts
    7
    Originally posted by Satya
    I think this will also fail as the database status is suspect you cannot access it to detach and attach.
    Yes. I am not able to do anything on that database as it is in suspect mode.

    By using data files can i retrieve data ?


    ricky

Posting Permissions

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