SQL Server Performance

Log Shipping Fails Not Sure Why....

Discussion in 'SQL Server Log Shipping' started by Invader Zog, Mar 3, 2004.

  1. Invader Zog New Member

    I set up log shipping through the SQL server enterprise manager GUI. It successfully created the database on the secondary server (I did select the option where the secondary server can become a primary). However, it does not appear that it is successfully importing the transaction logs.

    It looks like the transaction logs are being backed up fine on the primary server.

    It also looks like the transaction logs are being successfully copied from the primary to the back up.

    I assume that the failure has to do with the import.

    I'm not sure how to find out *why* it is failing. I've been looking through the logs and haven't found anything interesting.

    I found the job definition:

    EXECUTE master.dbo.xp_sqlmaint '-LSRestorePlanID "8DA5EC10-2B44-4818-8579-C5332999A7EB"'

    I tried running this from the SQL query analyzer and it gave me an error about sqlmaint.exe failing.

    Any suggestions on debugging??

  2. Invader Zog New Member

    I did notice, if I switched to the grid view that there was this message:

    GIMP = primary SQL server
    SQL-02 - secondary server

    Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194
    Copyright (C) Microsoft Corporation, 1995 - 1998
    Logged on to SQL Server 'SQL-02' as '[NT CREDENTIALS]' (trusted)
    Starting load for plan GIMP.whichdoyoulike_logshipping
    Source database - whichdoyoulike
    Destination database - whichdoyoulike
    [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.
    Loaded 0 files
    Finished load for plan GIMP.whichdoyoulike_logshipping
  3. satya Moderator

  4. Invader Zog New Member

    How ironic -- my attempts to monitor the success of the restore were what was responsible.

    It looks like it is working now. Next step -- figure out what it takes to start using the backup server as the primary in case the primary suffered a catastrophic failure.

  5. Invader Zog New Member


    It looks like when I go to the secondary machine under jobs and run the log shipping restore job, it works, however when it runs automatically (every 15 minutes), it fails.

    When I look in the ERRORLOG file on the secondary machine, I only see entries for when I manually ran it.

    The ERRORLOG on the primary machine only complains once the databases are out of synch for more than X minutes.

    Any suggestions for how to go about debugging this?

  6. satya Moderator

    To overcome the situation firstly stop the log shipping and restore full backup from primary to secondary server.

    Then re-start Log shipping process and ensure there are break-ups in file passing and restore on secondary server. Due to intermittent errors on secondary server it fails to restore the log.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.
  7. satya Moderator

    http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=1289 for similar issue.
    Transaction Log backups can only be restored if they are in a sequence. This sequence is determined by the LastLSN and FirstLSN fields that are returned by the RESTORE HEADERONLY command.

    Some of the most common reasons are:
    - There are redundant transaction log backup jobs on the primary server that are causing the sequence to be broken.
    - There are non-logged operations performed in the database.
    -The recovery model of the database was probably toggled between transaction log backups.
    -The Data Transformation Services (DTS) task on the primary server might be causing this problem.

    Satya SKJ
    This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

Share This Page