locked
Log Shipping unstability RRS feed

  • Question

  • I meet two problem about log shipping:

    my environment : os : windows server 2003; sql server 2005 sp2 

    about 20 servers be primary server (about 40~50 databases)

    only one server be the second server.

    1.

    While i complete the setting about log shipping, the second server will create two job LSCopy_XXX and LSRestore_XXX.

    but a few day later or a few hours later, the jobs will disappear. this problem only occurs for three primary servers,

            the other servers' jobs will not disappear. 

    After finishing the log shipping setting, i use profiler to record the msdb for sp_delete_job command.

    This command never appear even the job disappear again.

    2.

    Sometimes, while log shipping is running as the restore status, the status will not change back the normal status.

    In this case, we must reset the log shipping by full backup and setting.

    They result into the unstability of backup-restore. i already have no idea for them. anyone has some problem and solve them?

    • Moved by Shulei Chen Tuesday, September 25, 2012 2:32 AM (From:SQL Server Replication)
    Monday, September 24, 2012 5:17 AM

Answers

  • Hi,

    Please apply the latest service pack for SQL Server 2005 which you can get it from here. And then reconfigure the log shipping to check if it could help. Also you can monitor the current activity of all defined jobs on an instance of SQL Server 2005 by using SQL Server Agent Job Activity Monitor. For more details, please refer to this article: Monitoring Job Activity.


    Best Regards,
    Ray Chen

    • Proposed as answer by Shulei Chen Monday, October 8, 2012 9:52 AM
    • Marked as answer by AG5 Friday, October 19, 2012 3:56 AM
    Tuesday, October 2, 2012 11:08 AM
  • after i update the secondary server to SP4 version (the primary server can't update by human factors) and reconfigure the log shipping, 

    the job disappear again.

    and the Agent Job Activity Monitor couldn't find the history record of the job. Just like it never appear.

    this let me very confusing. is there any method to trace the log shipping job?

    Normally to track any such "magical" changes you need to enable auditing.
    Since this is happening with system tables, we need to put profiler trace (SQLDIAG) and keep it running till issue appears.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked as answer by AG5 Friday, October 19, 2012 3:56 AM
    Thursday, October 11, 2012 7:25 AM

All replies

  • Hi,

    Thanks for the post.

    1.
    According to you description, these two jobs LSCopy_XXX and LSRestore_XXX will disappear on three primary servers while you complete the log shipping. Base on my research, these two jobs will be created on secondary server after configure the log shipping. Please double check with this. For more details, please refer to this article: Understanding Log Shipping.


    If the Stored Procedure sp_delete_job never appear in the profiler, it means that these two jobs were not deleted manually. But they shouldn’t disappear without reason. Could you please check the job history if there have any related messages? Also does the job LSAlert exist on the secondary server? Do you receive any alerts?

    2.
    Please check the job history of LSRestore_XXX if there have any log messages. Please provide the messages for further troubleshooting if there have. Also maybe there’s dead lock or something on the secondary server so that the log shipping is always running as the restoring status, please check this. Here are some resources regarding configuring Log Shipping step by step. Please take a look at them as your reference:

    http://www.mssqltips.com/sqlservertip/2301/step-by-step-sql-server-log-shipping/
    http://www.sqlbackuprestore.com/logship2005_ssms.htm


    Best Regards,
    Ray Chen

    • Proposed as answer by Shulei Chen Monday, October 8, 2012 9:52 AM
    Tuesday, September 25, 2012 7:39 AM
  • For the point 1 :

    My LSCopy, LSRestore jobs are created on secondary server, not primary server after i finish the log shipping configuration on primary server computer.

    I don't configure the monitor server for log shipping because of the number of owned computer . And the LSAlter job doesn't show any fail message.

    I could get any message about those disappeared jobs in sysjobhistory table. Just like the message had gone while the job disappeared.

    Could any setting trace the certain job  for the reason of disappear on sql server 2005??

    For the point 2 :

    I will check again, 

    thank you!

    Friday, September 28, 2012 3:26 PM
  • Hi,

    Please apply the latest service pack for SQL Server 2005 which you can get it from here. And then reconfigure the log shipping to check if it could help. Also you can monitor the current activity of all defined jobs on an instance of SQL Server 2005 by using SQL Server Agent Job Activity Monitor. For more details, please refer to this article: Monitoring Job Activity.


    Best Regards,
    Ray Chen

    • Proposed as answer by Shulei Chen Monday, October 8, 2012 9:52 AM
    • Marked as answer by AG5 Friday, October 19, 2012 3:56 AM
    Tuesday, October 2, 2012 11:08 AM
  • after i update the secondary server to SP4 version (the primary server can't update by human factors) and reconfigure the log shipping, 

    the job disappear again.

    and the Agent Job Activity Monitor couldn't find the history record of the job. Just like it never appear.

    this let me very confusing. is there any method to trace the log shipping job?

    Thursday, October 11, 2012 6:07 AM
  • after i update the secondary server to SP4 version (the primary server can't update by human factors) and reconfigure the log shipping, 

    the job disappear again.

    and the Agent Job Activity Monitor couldn't find the history record of the job. Just like it never appear.

    this let me very confusing. is there any method to trace the log shipping job?

    Normally to track any such "magical" changes you need to enable auditing.
    Since this is happening with system tables, we need to put profiler trace (SQLDIAG) and keep it running till issue appears.

    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    • Marked as answer by AG5 Friday, October 19, 2012 3:56 AM
    Thursday, October 11, 2012 7:25 AM
  • Thank you very much, it happens with system tables. 

    And i find the reason of disappear jobs. Because the duplicate primary server name and database name.

    http://social.msdn.microsoft.com/Forums/zh-HK/240/thread/894d173b-849e-4078-bd15-c3a35dfd37af

    And i replace the LSCopy job by Ftp maybe cause the unstability, because the file don't complete while internet disconnect.

    (LSCopy also happens in this case) And the Backup and Restorewithout check the file by checksum.

    Now i will keep observing this problem.

    Friday, October 19, 2012 3:55 AM