SQL Server Performance

Transaction logs restore problem!

Discussion in 'SQL Server Log Shipping' started by mmuneebahmed, Sep 1, 2004.

  1. mmuneebahmed New Member


    Hi everyone.

    I have a problem and I appreciate if I can get some help on this from this forum...I have been using this forum and prepared my logshipping server..so I'm sure that I can get some help here once again.

    The following is the problem I'm getting when I restored my transaction logs.

    The database is marked suspect. Transaction logs cannot be restored. Use RESTORE DATABASE to recover the database.
    Server: Msg 3013, Level 16, State 1, Procedure mn_Restore_Log_Backup, Line 85
    RESTORE LOG is terminating abnormally.

    The cause of the problem is the unexpected power failure and now database is in suspected mode. I was wonder if there is a way I can restore my transaction logs from where the power went off. I don't want to restore the whole database, it's just the transaction logs I want to restore.

    I appreciate your time.

    Thanx

    gladiatr.
  2. Luis Martin Moderator

    Try to follow the procedure listed in books online to recover the suspect database:

    Execute sp_resetstatus.
    Use ALTER DATABASE to add a data file or log file to the database.
    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.

    Free disk space and rerun recovery.

    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 master
    GO
    sp_configure 'allow updates', 1
    GO
    RECONFIGURE WITH OVERRIDE
    GO

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

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

    sp_resetstatus can be executed only by the system administrator. Always shut down SQL Server immediately after executing this procedure.


    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.

  3. anupchintawar New Member

    In my opinion you need to run DBCC command. It will show the exact error why the database is suspected.
    Once you know the error you can take action depending on it.


    Regards
    -Anup
  4. satya Moderator

    Follow as suggested by Luis to reset the suspect status and then restore full backup from primary to secondary server, lastly log shipping to function properly.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  5. sundeip New Member

    Try to Detach and Reattach .

    using following SP..

    sp_Detach_db
    sp_attach_db


    Sandy

    (DB Developer)
  6. satya Moderator

    If the database is in suspect mode you cannot detach, you must reset the status in order to perform any action on that database.

    Satya SKJ
    Moderator
    http://www.SQL-Server-Performance.Com/forum
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page