SQL Server Performance

DB Mirroring AND Log Shipping together - log ship copy job fails

Discussion in 'SQL Server 2005 Database Mirroring' started by ewanovic, Jul 2, 2008.

  1. ewanovic New Member

    DB Mirroring AND Log Shipping together - log ship copy job fails, also log ship restore job fails
    Primary server = 64 bit SQL 2005 Std Edition
    Secondary server = 64 bit SQL 2005 Ent Edition
    Mirror setup already creates Log backups every 15 mins into share on SAN
    This share is accessible from Secondary server by SQL Agent account - can manually copy files and restore them as this account onto Secondary server successfully.
    Error Log from copy jobs : job reports success, EVEN finds and identifies the log file it is supposed to copy and restore, BUT then states number of files copied : 0
    Microsoft (R) SQL Server Log Shipping Agent
    [Assembly Version = 9.0.242.0, File Version = 9.00.3042.00]
    c Microsoft Corp. All rights reserved.

    2008-07-02 10:00:00.74 ----- START OF TRANSACTION LOG COPY -----
    2008-07-02 10:00:00.82 Starting transaction log copy. Secondary ID: 'fcd283eb-6d0b-4fcf-aeaf-4d4c3b45d294'
    2008-07-02 10:00:00.83 Retrieving copy settings. Secondary ID: 'fcd283eb-6d0b-4fcf-aeaf-4d4c3b45d294'
    2008-07-02 10:00:00.85 Retrieved copy settings. Primary Server: 'US01DB01', Primary Database: 'DBA', Backup Source Directory: '\us01db01DBA$', Backup Destination Directory: 'E:MSSQLLOGSHIPDESTN', Last Copied File: '<None>'
    2008-07-02 10:00:00.88 Copying log backup files. Primary Server: 'US01DB01', Primary Database: 'DBA', Backup Source Directory: '\us01db01DBA$', Backup Destination Directory: 'E:MSSQLLOGSHIPDESTN'
    2008-07-02 10:00:00.91 Checking to see if any previously copied log backup files that are required by the restore operation are missing. Secondary ID: 'fcd283eb-6d0b-4fcf-aeaf-4d4c3b45d294'
    2008-07-02 10:00:00.91 The copy operation was successful. Secondary ID: 'fcd283eb-6d0b-4fcf-aeaf-4d4c3b45d294', Number of log backup files copied: 0
    2008-07-02 10:00:00.93 ----- END OF TRANSACTION LOG COPY -----

    Exit Status: 0 (Success)
    Same for Restore job.
    Have verified windows file system permissions, SQL logins and permissions - crazy. Dont understand. Help !
    Only thing I have not tried yet is Cumulative Update Package 3 for SQL 2005 SP2 from Msoft
  2. satya Moderator

    Any other warnings or errors on the log?
    WHat account used between these 2 SQL instnaces?
  3. ewanovic New Member

    Hi, thanks for replying. Interesting issue.
    *** please pass this onto your members, struggled for days with this issue, and it looks like just a naming convention problem ***
    Jobs to copy log files and restore onto Secondary server use this Secondary server's SQL Agent account - a domain account, and an Admin on both servers. Permissions to file system are ok.
    Have this working now - explanation :
    Issue seems to be to do with naming of the Log files. If you create these outside of log shipping by some other method eg. SQL maintenance plan - the filename cannot be read by the LS copy job. Its like it cant see it.
    eg. filename = testing12_backup_20080708154108.trn - does not work
    If you let Log Shipping create the log backups itself - then filename is recognised and copy works.
    eg. filename = testing12_20080708154108.trn - this works !
    ( file created by log shipping backup job schedule that you setup when setting up log shipping )

Share This Page