Setting Up and Monitoring Log Shipping with T-SQL

A log shipping secondary can fall behind while every job on it reports success. On my test secondary, the restore ran (the same sqllogship command the job uses), found nothing, exited 0, and logged “The restore operation was successful.” An Agent job step wrapping that exit code would show success. The alert that should have fired depends on a SQL Server Agent job on the secondary, and that Agent wasn’t running.

Illustration of two harbour docks with boats carrying crates between them, and a woman harbour master with a stopwatch looking up at a dark signal lamp.

I set up log shipping on my workstation with T-SQL, then stopped it, broke it and fixed it, to see what each monitoring object reports. The primary was a SQL Server 2019 instance (15.0.4500.2, Developer). The secondary was a SQL Server 2025 instance (17.0.5015.4, Enterprise Developer) on the same machine. If you want the overview of where log shipping fits next to availability groups and the rest, start with SQL Server HA and DR Options: A Field Guide.

Setting It Up with T-SQL

Log shipping has three steps: back up the log on the primary, copy the file to the secondary, and restore it there.[1] The primary database must use the full or bulk-logged recovery model.[2]

I used bulk-logged because the 2019 instance runs a maintenance job that backs up the log of every full-recovery database every 15 minutes. Those backups would go to a different folder, and the secondary would never receive them. The job skips bulk-logged databases.

I made a full backup on the primary and restored it on the secondary WITH NORECOVERY. STANDBY wasn’t an option here. Microsoft documents that RESTORE WITH STANDBY isn’t supported for a database that needs an upgrade, which a 2019 database restored on 2025 does.[3] Then, on the primary:

Both instances are on one machine, so the “share” is a local folder. In production, @backup_share is the UNC path the secondary reads from. On the secondary:

I set both thresholds to 5 minutes so I could watch them trip, and turned the threshold alerts on. They’re off unless you ask for them.

What the Procedures Created

These are the jobs I found in msdb afterward:

Instance Job Enabled Schedule
Primary sat_LSBackup_sat_logship No None
Primary LSAlert_SQLLAB01\SQL2019 Yes Every 2 minutes
Secondary sat_LSCopy_sat_logship No None
Secondary sat_LSRestore_sat_logship No None
Secondary LSAlert_SQLLAB01\SQL2025 Yes Every 2 minutes

Microsoft documents that the backup job is created disabled, and that @threshold_alert_enabled defaults to 0.[4] The page for sp_add_log_shipping_secondary_database says the secondary’s default is the opposite: “A value of 1 (the default) means that the alert is raised.”[5] The code doesn’t agree. On both of my instances, OBJECT_DEFINITION shows @threshold_alert_enabled bit = 0 in the primary and the secondary procedure. If you want the alerts, pass 1 on both sides. The copy and restore jobs are created disabled too.[6] Calling the procedures alone gave them no schedule either, so nothing ships until you add schedules and enable the jobs. The alert jobs were enabled and scheduled at once. Each instance had two Agent alerts, “Log shipping Primary Server Alert.” for message 14420 and “Log shipping Secondary Server Alert.” for 14421, with no operator attached. On the 2025 instance the msdb log shows they were inserted during setup, inside the sp_add_log_shipping_secondary_database call. I couldn’t check the 2019 instance the same way, because a log backup had already truncated its msdb log.

Without a monitor server, each side keeps its own history. Backup history stays on the primary, and copy and restore history stays on the secondary. An alert job runs on each instance and checks only its own side.[7] A monitor server has to be added when log shipping is set up. Adding one later means removing the configuration and creating it again.[7] On my primary, sp_help_log_shipping_monitor returned a row for the secondary with the status and every time column NULL. Only is_primary, server and database_name were filled in.

Shipping Without the Agent

Agent was running on the 2019 instance and stopped on the 2025 instance, and I left it that way. Each job step is a call to sqllogship.exe, which you can run yourself:[8]

The GUIDs are the @primary_id and @secondary_id outputs. The backup, copy and restore at 12:29 each took about a second.

Before any backup had run, log_shipping_monitor_primary.last_backup_date already held the time I ran the setup procedure, and the status was 0. The secondary’s last_restored_date held its setup time too. last_backup_file is NULL until the first real backup, so check that too.

What Happens When Shipping Stops

After that first cycle I stopped running anything and polled sp_help_log_shipping_monitor on both instances every 20 seconds. The last backup finished at 12:29:18.863 and the last restore at 12:29:22.

Time Event
12:34:53 Both instances: 5 minutes since last backup or restore, status 0
12:35:13 Both instances: 6 minutes, status 1
12:36:01 Primary’s alert job raised 14420: “has not performed a backup log operation for 7 minutes”
12:37:36 Secondary: nothing raised, because its Agent was stopped. I ran sp_check_log_shipping_monitor_alert by hand and got 14421, “No restore was performed for 8 minutes”

The procedure’s code, which I read with OBJECT_DEFINITION, counts minute boundaries with DATEDIFF(minute, ...) and flags the row when that count is greater than the threshold. Depending on the second the backup finished, the flag can appear anywhere from 5 to 6 minutes after the last success. By the procedure’s arithmetic the status flipped at 12:35:00, about 5 minutes 41 seconds after the backup. My polls caught it between 12:34:53 and 12:35:13. The first 14420 came from the 12:36:01 run of the alert job and reached the error log at 12:36:01.020, 6 minutes 42 seconds after the backup. Its text said “7 minutes” because it uses the same minute-boundary count.

Both messages have is_event_logged = 1 in sys.messages, and the 14420 text appeared in the primary’s error log. The primary’s “Log shipping Primary Server Alert.” recorded two occurrences before I disabled its alert job at 12:48. It had no operator, so no notification was sent. On the 2025 instance no 14421 was logged by any job, because no alert job ran.

Successful Jobs That Don’t Clear the Status

At 12:37:44 I ran the copy and the restore on the secondary without a new backup on the primary. Both exited with code 0. The copy logged “Number of log backup files copied: 0” and the restore logged “Number of log backup files restored: 0”. Neither updated last_copied_date or last_restored_date, so the status stayed at 1.

Next I took a backup at 12:37:59 and copied it straight away, then held the restore until 12:45:11. Right after that restore, time_since_last_restore was 0 and last_restored_latency was 8 minutes, and the status was still 1. Latency is the time from the creation of the last backup to its restore.[9] The procedure’s code flags a secondary when either the time since the last restore or the latency is greater than the threshold. The 14421 message I raised by hand reported “No restore was performed for 0 minutes” and “Restored latency is 8 minutes”.

A Failed Restore with a Healthy Status

The last test was a log backup outside log shipping. On the primary I ran BACKUP LOG to a different folder, then ran the log shipping backup, copy and restore. sqllogship exited with code 1. The monitor status was already 1 from the previous test’s latency, which is why I repeated the gap after a clean restore. The restore logged this to log_shipping_monitor_error_detail:

The history row for that session has session_status 3, which the documentation defines as Error.[10] The missing file shows up in msdb.dbo.backupset on the primary, with its path in backupmediafamily. I restored it by hand WITH NORECOVERY, and the next log shipping restore succeeded.

Then I repeated the gap straight after a clean restore. The restore failed at 12:45:50, and sp_help_log_shipping_monitor still returned status 0. sp_check_log_shipping_monitor_alert raised nothing. At 12:51:09, once the restore threshold had passed, the status was 1 and the alert check raised 14421 with “No restore was performed for 6 minutes”. The status and the alert check look only at elapsed time and latency.

A log backup taken outside log shipping breaks the chain the secondary restores from, unless it uses COPY_ONLY.[11] If a separate job backs up the log of a log-shipped database, the secondary stops at the first gap. A check that only asks when the last log backup ran, like the ones in Log Backup Failures - Danger, Will Robinson! and Full Recovery without Log Backups? Get Notified!, may report that database as healthy. It sits in backupset with type L and a path (backupmediafamily.physical_device_name) outside the log shipping folder, so that’s the column to check.

A Monitoring Query

This query reads the local monitor tables, so it works on the primary or on a secondary. It should work on a monitor server too, but I didn’t test one. Only the elapsed-time test uses seconds. The latency test uses whatever the table holds, and that value counts minute boundaries: the restore I held for 7 minutes 12 seconds was stored as 8. A secondary flagged for latency stays flagged until a restore with low latency. At 12:51:12 the query flagged my caught-up secondary because its stored latency was 6. The query also flags an error logged after the last success.

At 12:46:22, after the second failed restore, the secondary returned over_threshold 0 and error_since_last_success 1, with the “could not be applied” message. Half a minute earlier, at 12:45:50, sp_help_log_shipping_monitor had shown status 0 for the same secondary.

Limits of This Test

  • I tested one build of each version, on one workstation, with local folders instead of a network share. Share permissions and network copy times weren’t part of it.
  • I ran the jobs’ commands by hand rather than through Agent schedules. Agent ran only the primary’s alert job. No Agent job ran on the secondary: the copy and restore were hand runs of sqllogship.exe, with exit codes but no job history.
  • I disabled the primary’s alert job at 12:48 to stop the repeated 14420 entries, so the alert occurrence count covers only the first 20 minutes of the test.
  • I didn’t configure a monitor server. The Microsoft page in note 6 says that with one, a single alert job on the monitor covers the whole configuration. I didn’t test that.
  • I didn’t attach operators to the alerts, so I didn’t test notification delivery.

What I’d Check on a Real Secondary

Confirm Agent is running on every secondary, or include a monitor server when you set up log shipping, because the 14421 check runs on the secondary when there’s no monitor. Attach an operator to the 14420 and 14421 alerts. Watch for errors in log_shipping_monitor_error_detail as well as the status column, because a failed restore leaves the status at 0 until the threshold passes. And find out whether anything else backs up the log of a log-shipped database.

Related: WSFC Health Checks for SQL Server DBAs: PowerShell and DMVs (available 2026-11-20).

If you monitor log shipping some other way, I’d like to hear how. Find me on Bluesky or LinkedIn.

References

  1. About Log Shipping (SQL Server) - Microsoft Learn. Describes the backup, copy and restore operations, the optional monitor server, and the alert job. ↩
  2. Configure Log Shipping (SQL Server) - Microsoft Learn. States that the primary database must use the full or bulk-logged recovery model. ↩
  3. Upgrade SQL Server Log Shipping - Microsoft Learn. States that RESTORE WITH STANDBY isn’t supported for a database that requires upgrading. ↩
  4. sys.sp_add_log_shipping_primary_database (Transact-SQL) - Microsoft Learn. Documents the threshold parameters, the default of 0 for @threshold_alert_enabled, and that the backup job is created disabled. ↩
  5. sys.sp_add_log_shipping_secondary_database (Transact-SQL) - Microsoft Learn. States that a value of 1 is the default for @threshold_alert_enabled on the secondary. ↩
  6. sys.sp_add_log_shipping_secondary_primary (Transact-SQL) - Microsoft Learn. States that the copy and restore jobs are created disabled. ↩
  7. Monitor Log Shipping (Transact-SQL) - Microsoft Learn. Explains where history is stored, that without a monitor server an alert job runs on the primary and on each secondary, and that a monitor server must be added when log shipping is enabled, or the configuration removed and replaced to add one later. ↩
  8. sqllogship Application - Microsoft Learn. Syntax for the -backup, -copy and -restore options and where to find the IDs they take. ↩
  9. sys.sp_help_log_shipping_monitor (Transact-SQL) - Microsoft Learn. Defines the result set columns, including status, time_since_last_restore, and last_restored_latency as the time from creation of the last backup to its restore. ↩
  10. log_shipping_monitor_history_detail (Transact-SQL) - Microsoft Learn. Defines agent_type (0 backup, 1 copy, 2 restore) and session_status (3 = Error). ↩
  11. Copy-Only Backups (SQL Server) - Microsoft Learn. States that a copy-only log backup preserves the existing log archive point and doesn’t affect the sequencing of regular log backups. ↩