SQL Server Performance

log shipping role reversal problem

Discussion in 'SQL Server Log Shipping' started by shahab03, Jun 13, 2006.

  1. shahab03 Member

    changing Logshipping roles seems easy from BOL...but I ve experienced lots of hurdles in each step....
    I am not able to execute:

    EXEC sp_change_secondary_role
    @db_name = 'test',
    @do_load = 1,
    @force_load = 1,
    @final_state = 1,
    @access_level = 1,
    @terminate = 1,
    @stopat = NULL
    GO



    when I executed this SP, I got an error:

    Server: Msg 22029, Level 16, State 1, Line 0
    sqlmaint.exe failed.


    Per following

    http://support.microsoft.com/default.aspx?scid=kb;en-us;294397&Product=sql2k

    I can resolve the issue above by

    1)Ensure that there are no outstanding transaction logs before you execute the sp_change_secondary_role stored procedure. Manually running the RESTORE job on the secondary server before you run sp_change_secondary_role ensures that there are no outstanding transaction logs.

    2) You could pass the value of zero to the @terminate parameter (@terminate = 0) while you execute the sp_change_secondary_role stored procedure. However, the user must verify that there are no users connected to the database before the stored procedure is executed to ensure that the RESTORE LOG works successfully.




    I would like to choose option 1. However I am not sure whats the best to find which transaction logs need to be applied and how to apply them? any examples would be wonderful.
    thanks
  2. satya Moderator

    Option 1 is better and upto the time of disaster on primary server make sure the Tlog are restored on secondary server, as you can see the list of files according to date & time.

    Refer to books online for RESTORE LOG statement and point in time restore topics.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  3. shahab03 Member

    Is sql server intelligent enough to recognize which logs it needs or i need to restore with norecovery option?
  4. satya Moderator

    The user must be intelligent to restore the order of the logs if performed manually and log shipping will take care of the log restores. They will be restored by looking at Last and first LSN between the Log backups. Refer to books online for more information on the Transaction log architecture.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
  5. shahab03 Member

    I see. I was thinking it has Oracle like architecture where it knows where to start from.
  6. satya Moderator

    IN the log shipping process the system will take care of that and if any breakup occurs you must restore Tlogs from the last database restore onwards.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided �AS IS� with no rights for the sake of knowledge sharing.

Share This Page