SQL Server Performance

Out of Sync

Discussion in 'SQL Server Log Shipping' started by buenowight, Jun 25, 2007.

  1. buenowight New Member

    Hi,

    We are implementing log shipping from production to a stand by server, but yesterday two of our databases went "out of sync", when i looked at the jobs on the stand by server, the copy job( which copies the transaction log backup from prod to stand by server) is running successful, however, the restore job is failing continuously.

    I took a full backup from PROD and restored the backup on the stand by server( assuming that this will do the trick) but still the restore job for these two databases is failing.

    Can any one suggest me the correct sequence I need to follow if i have to get these two datbases in sync??

    Please correct me if i listed the sequence wrong:

    1. take a full backup of the database from production

    2. copy the backup file from prod server to stand by server

    3. disable the copy job (which copies the transaction log from prod to standby) as well as the restore job

    4. apply the full back up on the stand by server

    5. enable the copy job and run it

    6. enable the restore job and run it.

    Please let me know if i missed some thing or f i am doing some thing wrong...

    Your respose is highly appreciated.

    Regards,
    Bueno
  2. buenowight New Member

    Please let me know the best way I need to follow, to get these databases back to sync.
  3. satya Moderator

    Can you tell us why that RESTORE job failed on standby server, any error or warning.
    Your sequence of steps looks ok, but ensure to restore the database using WITH NORECOVERY in order to restore logs.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  4. buenowight New Member

    when I check the view job history Its showing this error :

    "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."

    when I checked in the log shipping monitor its showing this error:

    "[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3101: [Microsoft][ODBC SQL Server Driver][SQL Server]Exclusive access could not be obtained because the database is in use.
    [Microsoft][ODBC SQL Server Driver][SQL Server]RESTORE LOG is terminating abnormally."


    Apart from investingating the error, I need to get these databases back to sync, can you please suggest me a way to do this??

    Thanks & regards,

    Bueno.

  5. satya Moderator

    There you go because the database is in use on SECONDARY server the restore job failed, so you have to drop all the users. Refer to thishttp://www.sql-server-performance.com/faq/sqlviewfaq.aspx?faqid=117 FAQ in thsi case.

    If you wanted to use secondary server for reporting purpose then ensure to restore database using STANDBY clause, during the log restore the connections must be dropped in order to continue and you can include this script in the transaction log without any manual intervention.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  6. buenowight New Member

    Thanks for your Help!!

    But I am still suspecting the sequence I should follow to get the databases in sync.I would appreciate if you could verify....

    1. I have taken full backup on prod,
    2. disabled the TRN log back up job on PROD, ( so presently there are not TRN backup being taken on the databases on PROD)
    3. Copying the backup file to secondary server,
    Then
    4. I will kill all connection to the database on secondary server and finish the restore,
    5. I will start the TRN log backup job on the PROD server, as soon as it finishes,
    6. I will kick off the Copy job on secondary server ( to get the TRN log backup from prod to secondary server)
    7. As soon as this is completed,
    8. I will kick off the Restore job (which has been failing all this while)

    Correct???

    Thanks.
  7. satya Moderator

    Flip the 1 & 2, say disable the transaction log job and perform full database backup. To kill users on secondary server you can also use
    ALTER DATABASE <x> SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    .

    Also check there aren't any non-logged operations within these log backups schedule, as it will invalidate the process.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  8. Deepak Rangarajan New Member

    Dear Friend,
    I just have a doubt,all you mentioned is absolutely right.But the in last step i don't think u need to start the restore job manually if the job is already enabled it will automatically restore the trn logs,if they are in sequence.....

    MY QUESTION
    ***********
    if the backup and copy job succeeds and restore job fails due to mismatch in lsn,assume a particular tran log is missing in destination.....if the same tran log is available in source is it ok if i just copy that particular tran log from source into the destination????the restore job will automatically restore it na??? or else should i manually restore all tran logs in destination????pls advice

    Regards
    Deepak
    SQL DBA
  9. satya Moderator

    Without enabling the restore job on secondary server how you can restore logs?
    If there is a mismatch in the LSN then you have to restore the last backup with corresponding transaction log backup.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  10. Deepak Rangarajan New Member

    Satya,its obvious that we need to enable the restore job......fyi its already in enabled state....but it is getting failed as i mentioned earlier due to a particular LSN i.e the 1 which was copied recently was deleted accidentally by some user(just assume it) b4 that lsn was restored....now is it ok if i copy that lsn alone from source server into the destination????the restore job will restore all trn logs automatically rite???

    Regards
    Deepak
    SQL DBA
  11. satya Moderator

    The restore job will not restore if you have copied, in order to get to the latest LSN you have to perform a manual restore from database bacup until the latest trnsaction log backup.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  12. Deepak Rangarajan New Member

    Sathya,Many thanks for the info

    Regards
    Deepak
    SQL DBA

Share This Page