Sql-server – Why does Log Shipping .TRN file copy just stop

log-shippingsql serversql-server-2008-r2

I apologize in advance for a long post, but I have had it up to here with the situation of having to delete Log Shipping (LS) configuration and starting it over for any DB that has got this error.

I have LS setup on 3 win2k8r2 servers (primary, secondary, monitor) with 100 databases backed up and shipped transactions from the primary to secondary and monitored by monitor. Backups and copies are run every 15 mins and then the ones older than 24 hrs are deleted.

Some DBs are very active and some not so active but shipped regardless for uniformity sake (basically to make secondary server identical to primary). Some DBs are for SharePoint 2010 and the majority are for inhouse apps.

The issue is that after all LS configs are set up, everything works well for about 3 to 4 days. Then I go to the Transaction LS Status report on the secondary, I see that randomly some LS jobs have an Alert Status, because the time since last copy is over 45 mins so no restore has occurred. This seems random and the only errors I see is from a SharePoint 2010 DB (WebAnalyticsServiceApplication_ReportingDB_77a60938_##########) which I belive is a Reporting DB that gets created weekly and LS just cannot figure out which the last copy to backup or to restore is.

I posted here regarding that and I have yet to find a permanent solution.

For my main error (time since last copy) I have not seen anything that could have caused that and I don't get any messages (even though some alert statuses have been ignored for 3 days).

Anyway, I would really appreciate any input on understanding what's causing this issue and how I could fix it.

Best Answer

Restarting both the source and destination servers housing the sql engines seemed to fix the issue. its been a week and all backup, copy and restore has no issues except for just that one SharePoint reporting db WebAnalyticsServiceApplication_ReportingDB_77a60938-####-####-####-##########. Thanks to everyone for their input, as I at least came out of this more knowledgeable.

Related Solutions

SQL Server – Log Shipping and New NDF File

if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If logshipping paths are same on the primary and secondary servers then adding an ndf will be seamless.

If the paths are different on primary and secondary servers then

Stop the backup, copy and restore jobs related to the database that you are adding a secondary file.
Add the ndf file on primary using ALTER DATABASE db_name ADD FILE..
run the backup job manually on the primary server. This will generate a T-log backup. Make sure that all the previous log backups are applied to the secondary.
Run the copy job manually so that the T-log with above changes is shipped to secondary server.

Manually restore the log file (from above step) using the

RESTORE log db_name FROM      Disk='path_T-log_from_Above_Step.trn'
with norecovery,  ---> Very Important .. !!
MOVE 'logical_file_name_of_ndf'
TO 'physical_path\secondary_file_name.ndf'

Once above steps are successful, enable all the backup, copy and restore jobs for the database. All the subsequent backup, copy and restore jobs should be successful now.

SQL Server Log Shipping – Monitor Server Down, Copying but Not Restoring

As per error it does not necessarily indicate a problem with log shipping. The message indicates that the difference between the last backed up file and current time on the monitor server is greater than the time that is set for the Backup Alert threshold. Log shipping is out of synchronization beyond the backup threshold.

Instead, this message might indicate below problem:

The backup job is not running. Possible causes for this include the following: the SQL Server Agent service on the primary server instance is not running, the job is disabled, or the job's schedule has been changed.

The following list includes some of these reasons:

The date or time (or both) on the monitor server is different from the date or time on the primary server. It is also possible that the system date or time was modified on the monitor or the primary server. This may also generate alert messages
When the monitor server is offline and then back online, the fields in the log_shipping_primaries table are not updated with the current values before the alert message job runs. This seems to be the cause of LS going out of sync in you're case.
The log shipping Copy job that is run on the primary server might not connect to the monitor server msdb database to update the fields in the log_shipping_primaries table. This may be the result of an authentication problem between the monitor server and the primary server.

It was preventing the secondary instance from applying the backups even though I later explicitly ran the restore job. When the monitor server came back on - everything sorted itself out.

When the monitor server instance goes offline and then comes back online, the log_shipping_monitor_primary table is not updated with the current values before the alert message job runs. To update the monitor tables with the latest data for the primary database, sp_refresh_log_shipping_monitor on the primary server instance ran and sync you're LS status.

Best Answer

Related Solutions

SQL Server – Log Shipping and New NDF File

SQL Server Log Shipping – Monitor Server Down, Copying but Not Restoring

Related Question