Sql-server – Log shipping 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER

clusteringlog-shippingrestoresql servertransaction-log

We have recently configured one database to be replicated with log shipping in production, using SQL Server 2008 R2.

We have up and running more than 150 database replicas without any problems. But now, when we try to configure the new LS replica in a new server environment we have experiencing one error:

The log shipping is configured from a 2008 r2 SP2 server to a 2008 r2 SP3 CLUSTER (the only difference compared with our other replicas)

  1. the LS backup works Ok in the primary
  2. the LS Copy works OK and successfully copy the files from the primary to the secondary

  3. the LS Restore restores one, two or three trn files, and than it fails:

    Restored log backup file.

    Secondary DB: 'xxxxyyyy_mirror'
    File: 'P:\ xxxyyyy _TLogs_Mirror_LS\xxxyyyy\ xxxyyyy _20151120163351.trn'

    The restore operation was successful. Secondary Database: ' xxxyyyy _mirror', Number of log backup files restored: 1

    The next job execution:

    Error: An error occurred while processing the log for database ' xxxyyyy _mirror'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

    RESTORE LOG is terminating abnormally.
    Processed 0 pages for database xxxyyyy _mirror', file 'Vitacare2k6_Dev' on file 1.

    Processed 1 pages for
    database ' xxxyyyy _mirror'
    file 'Vitacare2k6_Dev_log'

    on file 1.

    (Dot Net SqlClient Data Provider)

    And then the database gets into Suspect Mode

  4. the files (MDFs, LDFs and TRNs) are in an Clustered Disk shared by the cluster nodes.

I tried to restore the TRNs in the same full backup from the cluster my PC and I get no errors.

Can you guys provide me any guidance on where I could troubleshoot this further, I would greatly appreciate it.
Thanks for your time and input.
Rafael

Notes: I tried to restore the files in other instance (my Laptop) and i get no error, so i do not believe in corruption
I also tested in restore the Sp2 to an SP2 instance manually and sp2 to sp3 instance with no errors.

Best Answer

Logshipping is a death tested technique that has been around since many ages.

Looking at your specific error sequence on secondary server

  • The log restores errors out after a couple of successful restores.
  • The database goes into suspect mode
  • you tried to restore the log files and they restore it fine, so the log files are not corrupt.

You should check if the Bytes per Physical Sector on secondary server for the disk that you are trying to restore is NOT 512 bytes.

You can install the Hotfix for SQL Server 2008 R2 SP2 or correct the disk bytes per physical sector.

Related Question