Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2003
    Posts
    176

    Unanswered: suspect database

    I could not open my database.It says its Suspect and could not open and not displaying any of the objects
    What should be the reason and what is steps i can do make it up and running.

    Thanks

  2. #2
    Join Date
    Apr 2003
    Posts
    176
    WHen i run EXEC sp_resetstatus 'dbname' i got the below message.

    Prior to updating sysdatabases entry for database 'ihatespamdb', mode = 0 and status = 4194333 (status suspect_bit = 0).
    No row in sysdatabases was updated because mode and status are already correctly reset. No error and no changes made.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    If you're still around, check your errorlog. Depending on the error entry there we may OR may not fix it relatively painlessly.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Apr 2003
    Posts
    176
    update sysdatabases set status = 32768 where name = 'pubs'

    When i run the above command I got the following server message.

    Ad hoc updates to system catalogs are not enabled. The system administrator must reconfigure SQL Server to allow this.


    How can I enable ad hoc updates on the server.

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    From BOL

    Allow Updates Option
    Use the allow updates option to specify whether direct updates can be made to
    system tables. By default, allow updates is disabled (set to 0), so users
    cannot update system tables through ad hoc updates. Users can update system
    tables using system stored procedures only. When allow updates is disabled,
    updates are not allowed, even if you have the appropriate permissions
    (assigned using the GRANT statement).

    If you set allow updates to 1 using the sp_configure system stored procedure,
    you must use the RECONFIGURE WITH OVERRIDE statement. This setting takes effect
    immediately (without a server stop and restart).

    So ...
    sp_configure 'allow updates', 1
    GO
    RECONFIGURE WITH OVERRIDE
    GO

    <do your dirty work>

    sp_configure 'allow updates', 0
    GO
    RECONFIGURE WITH OVERRIDE
    GO

  6. #6
    Join Date
    Apr 2003
    Posts
    176
    Thanks,
    It worked fine.But i can not open the db in Eneterprise manager.I have copied all the user tables and it s data from the query analyzer.But I have some stored procedures in the DB .how can i get the text of the stored procedures.

    Thanks.

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Do you have a backup of the database that is good. If so, restore it under another name and script out the sprocs.

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Apr 2003
    Posts
    176
    I dont have a backup.Its the real problem.

  9. #9
    Join Date
    Jul 2004
    Posts
    191
    I had the same problem I didn't have a backup copy, but I was able to attach the database using:

    sp_attach_db [ @dbname = ] 'dbname'
    , [ @filename1 = ] 'filename_n' [ ,...16 ]

    hope that helps

    Lystra

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    SO you have reset the status and can't get to it from EM. Can you get to it from QA. If so, select everything from syscomments and see if you can salvage the procs from there. You will be looking at the ctext column for the most part.

    BEWARE ... HERE LIE DRAGONS.

    Tom

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    sp_attach_db will not work if the db is in Suspect mode. Suspect results from data or log device corruption. What I was referring to was the situation when log device got trashed. If the problem is with the data device I hope someone else may have a solution, but from what I've seen so far, - only MS PSS tool of recovering db pages did the trick. But let's think positively, hey?! Let's hope it's you log device. In this case:

    1. Detach your db
    2. Rename the existing log device to something else
    3. in QA type the following: exec sp_attach_single_file_db 'your_db', 'complete_path_for_mdf_file'
    4. in QA type:
    exec sp_dboption 'db_name', 'single', true
    dbcc checkdb('db_name') with physical_only
    exec sp_dboption 'db_name', 'single', false
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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