locked
Sql Server log shipping RRS feed

  • Question

  • We have setup log shipping from production environment to the test environment, which has been running well till now. we have set up a sql server job which copies logs from a shared folder( which has the latest logs transferred from production), to the test server folder. Now when we try to run the job it does not throw any error message nor copies any transaction logs from the shared folder to the local folder(test environment) returns 0 records. It identifies the last copied transaction log file, but it is not copying the logs after that last copied transaction log file.

    We were able to restore the transaction logs manually to the secondary server. Please let me know what is wrong. This is the message when i try to execute the copy job from command prompt.



    C:\Documents and Settings\sqlcluster>"D:\Program Files\Microsoft SQL Server\90\T
    ools\Binn\sqllogship.exe" -Copy E8B9D6A7-3DDC-40BD-A6C9-EBB6DEEDA9F5 -server DA1
    \ACCTVIEWER

    Microsoft (R) SQL Server Log Shipping Agent
    [Assembly Version = 9.0.242.0, File Version = 9.00.4035.00]
    c Microsoft Corp. All rights reserved.

    2010-01-25 02:58:24.89  ----- START OF TRANSACTION LOG COPY   -----
    2010-01-25 02:58:24.95  Starting transaction log copy. Secondary ID: 'e8b9d6a7-3
    ddc-40bd-a6c9-ebb6deeda9f5'
    2010-01-25 02:58:25.12  Retrieving copy settings. Secondary ID: 'e8b9d6a7-3ddc-4
    0bd-a6c9-ebb6deeda9f5'
    2010-01-25 02:58:25.12  Retrieved copy settings. Primary Server: 'G2SQL500\MAIN'
    , Primary Database: 'users', Backup Source Directory: '\\Wye\transfer\corp\users
    ', Backup Destination Directory: '\\da1\da1\transfer\corpusers', Last Copied Fil
    e: '\\da1\da1\Transfer\Corpusers\Users_20100119190000.trn'
    2010-01-25 02:58:25.14  Copying log backup files. Primary Server: 'G2SQL500\MAIN
    ', Primary Database: 'users', Backup Source Directory: '\\Wye\transfer\corp\user
    s', Backup Destination Directory: '\\da1\da1\transfer\corpusers'
    2010-01-25 02:58:25.25  Checking to see if any previously copied log backup file
    s that are required by the restore operation are missing. Secondary ID: 'e8b9d6a
    7-3ddc-40bd-a6c9-ebb6deeda9f5'
    2010-01-25 02:58:25.26  The copy operation was successful. Secondary ID: 'e8b9d6
    a7-3ddc-40bd-a6c9-ebb6deeda9f5', Number of log backup files copied: 0
    2010-01-25 02:58:25.28  ----- END OF TRANSACTION LOG COPY     -----

    Monday, January 25, 2010 11:08 AM

Answers

  • Hi,

    Based on your description, the problem is the log shipping service cannot move the log files to secondary server. Do you create any maintenance plan to back up the transaction log for the same database? If so, it will affect the log shipping working. To work around the issue, you could perform the full and differential database backups instead of the transaction log backups. For more information, please see http://msdn.microsoft.com/en-us/library/ms175480(SQL.90).aspx.

    If you have any more questions, please let me know.
    Thanks.


    ***Xiao Min Tan***Microsoft Online Community***
    Thursday, January 28, 2010 8:47 AM