Post reply

Suspect Database - Need Help!!

  • May 10, 2005 at 3:19 pm

    #88395

    HELP!!  We have a Windows 2000 w/SP4 and MSSQL 2000 w/SP3a.  Developer called me today and said he has a server that has a suspect database.  Claims he was executing some create table scripts and the server seemed like it was non-responsive. He said he rebooted and when the server came back online his database was suspect.  The following errors are present in the log:

    Error while undoing logged operation in database 'dbname'. Error at log record ID (26990:18320:431)..

    Error: 3314, Severity: 21, State: 3

    An error occurred while processing the log for database 'dbname'..

    Error: 9004, Severity: 23, State: 2

    Supposedly they had tried to detach and retach the database with no luck.  Wish I would have got called before that happened.  At this point I copied the original log file to separate directory and then deleted the file and tried a single_file_db attach with the same errors. 

    Of course the big kicker, nobody bothered to create any type of backups.  Got to love coming to a new shop where you are the only DBA.  Any help would be greatly appreciated.

    -Keith

  • May 10, 2005 at 3:50 pm

    #557910

    Hello,

    When you say No backups did you mean No SQL Backups or No Windows Backups or both? If the server had Windows backups and SQL Server was stopped during one of Windows backups you may have database files somewhere on the tapes.

    Regards,Yelena Varsha

  • May 10, 2005 at 4:35 pm

    #557914

    We may have a windows backup...Still waiting to hear back on that.  But definitely no database backups.

  • May 10, 2005 at 5:11 pm

    #557916

    Hello,

    Have you tried executing DBCC to check for consistency errors ?

    Something like this :

    In SQL Query Analyzer:

    a) place database in single-user mode

    exec sp_dboption dbname,'single user',true

    b) repair database

    DBCC CHECKDB ('dbname',REPAIR_REBUILD)

    c) if the results indicate that there is still a consistency error

    DBCC CHECKDB ('dbname',REPAIR_ALLOW_DATA_LOSS)

    d) remove single-user mode

    exec sp_dboption dbname,'single user',false

    After clearing error make sure to backup DB (with verify check) in question.

    Hope this helps

  • May 10, 2005 at 6:12 pm

    #557917

    When a database is in suspect mode you can't run DBCC commands against it.  However I have come up with a solution to get the database online and the log file rebuilt.  I am currently checking the consistency and I will post my findings back to this thread should everything work.

    Thanks for your input.

    -Keith

  • May 10, 2005 at 6:24 pm

    #557919

    Can't let developers run create table in production, much less REBOOT THE SERVER! I would be in there with a big whip and a loudspeaker.

  • May 10, 2005 at 6:29 pm

    #557920

    Can't let developers run create table in production, much less REBOOT THE SERVER! I would be in there with a big whip and a loudspeaker.

  • May 10, 2005 at 6:31 pm

    #557921

    Couldn't agree more!!  Just wish I had the political clout to raise some hell and get more policies established.  If I was unable to recover the database it would have been a lot easier to have the political push to get something done.

  • May 10, 2005 at 6:33 pm

    #557922

    Can't let developers run create table in production, much less REBOOT THE SERVER! I would be in there with a big whip and a loudspeaker.

  • May 11, 2005 at 12:34 am

    #557939

    Your database is victim of TORN_PAGE_DETECTION.

    You should restore from backup but as you do not have a backup try using

    sp_resetstatus database_name. Remember to reboot the server before trying to access the database.

    Do let us know if this help you.

     

    Kindest Regards,

    Amit Lohia

  • May 11, 2005 at 1:48 am

    #557954

    here is what you can use... replace replace the appropriate values

     

    Microsoft® SQL Server™ 2000 returns error 1105 and sets the status column of sysdatabases to suspect if SQL Server is unable to complete recovery on a database because the disk drive no longer has any free space. Follow these steps to resolve the problem:

    1. Execute sp_resetstatus.
    2. Use ALTER DATABASE to add a data file or log file to the database.
    3. Stop and restart SQL Server.

      With the extra space provided by the new data file or log file, SQL Server should be able to complete recovery of the database.

    4. Free disk space and rerun recovery.

    sp_resetstatus turns off the suspect flag on a database, but leaves all other database options intact.

    Caution  Use sp_resetstatus only when directed by your primary support provider or as recommended in Troubleshooting. Otherwise, you might damage your database.

    Because this procedure modifies the system tables, the system administrator must enable updates to the system tables before creating this procedure. To enable updates, use this procedure:

    USE masterGOsp_configure 'allow updates', 1GORECONFIGURE WITH OVERRIDEGO

    After the procedure is created, immediately disable updates to the system tables:

    sp_configure 'allow updates', 0GORECONFIGURE WITH OVERRIDEGO

    sp_resetstatus

  • May 11, 2005 at 2:56 am

    #557984

    That developer stuff is unreal ... You should know better! That's why I like to do all on my own - never had a db problem nor a DBA to set it up for me!! If you are unhappy with your developers try changing - if you cannot - maybe they'll change you!! Eheh ...

    Unreal man

  • May 11, 2005 at 9:18 pm

    #558252

    visist SQLTeam.com and find How to attach DB with corrupt.ldf

    fellow there method

    thank

    Nevil

    Sri lanka

Viewing 13 posts - 1 through 12 (of 12 total)

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