Quick Scan Report – Excessive Log Shipping History

What this check looks for

The number of rows in the log shipping history tables in msdb, against a threshold that defaults to 250,000 rows. If the DBHealthHistory database is present, the threshold can be configured there.

Why it matters

Log shipping writes history for every backup, copy and restore operation, on every database, on every schedule interval. It is the highest volume history generator in msdb.

The arithmetic is the reason. A log shipping configuration with backup, copy and restore jobs running every 15 minutes writes roughly:

That is around 300 rows per database per day before the monitor detail, so ten shipped databases produce a million rows a year, and the monitor history multiplies it further.

What it costs:

• msdb grows, on both the primary and the secondary, and on the monitor server if you have one.
• The log shipping status report becomes slow, which is the report you use to check whether log shipping is healthy. That is the practical symptom.
• The log shipping jobs themselves slow down, since each one inserts into these tables.
• msdb maintenance takes longer.

The cleanup mechanism exists and is per configuration, which is the thing worth knowing. Log shipping has its own history_retention_period, set in minutes, and the log shipping jobs call sp_cleanup_log_shipping_history themselves using that value. So unlike backup history, this one does clean up, and the finding usually means the retention period is set very high or the cleanup has been failing.

The default retention is 14420 minutes, which is about ten days. A configuration showing millions of rows either has a much larger value, has cleanup failing, or has a monitor server whose history is not being trimmed.

And there is a more important thing to check while you are here. Log shipping history is how you find out whether log shipping is actually working. A secondary that has not restored in three days is a far more serious finding than a large history table, and the same queries answer both questions. Read the latency before you purge anything.

How to confirm it yourself

Start with whether log shipping is healthy, which matters more than the table size:

-- how far behind each secondary is
SELECT [primary_server],
       [primary_database],
       [secondary_server],
       [secondary_database],
       [last_backup_file],
       [last_copied_file],
       [last_restored_file],
       [last_backup_date],
       [last_copied_date],
       [last_restored_date],
       DATEDIFF(MINUTE, [last_restored_date], GETDATE()) AS [restore_lag_minutes]
  FROM msdb.dbo.log_shipping_monitor_secondary WITH (NOLOCK);

SELECT [primary_server], [primary_database],
       [last_backup_file], [last_backup_date],
       DATEDIFF(MINUTE, [last_backup_date], GETDATE()) AS [backup_lag_minutes],
       [backup_threshold], [threshold_alert_enabled]
  FROM msdb.dbo.log_shipping_monitor_primary WITH (NOLOCK);

A lag well beyond the threshold is the finding to act on first.

Then the history volume:

USE [msdb];
GO
SELECT OBJECT_NAME(p.[object_id]) AS [table_name],
       SUM(p.[rows])              AS [rows],
       CAST(SUM(a.[total_pages]) * 8.0 / 1024 AS DECIMAL(12,1)) AS [size_mb]
  FROM sys.partitions            AS p WITH (NOLOCK)
 INNER JOIN sys.allocation_units AS a WITH (NOLOCK) ON a.[container_id] = p.[partition_id]
 WHERE OBJECT_NAME(p.[object_id]) LIKE 'log_shipping%'
   AND p.[index_id] IN (0, 1)
 GROUP BY p.[object_id]
 ORDER BY [size_mb] DESC;

How far back it goes:

SELECT MIN([log_time]) AS [oldest], MAX([log_time]) AS [newest], COUNT(*) AS [rows]
  FROM msdb.dbo.log_shipping_monitor_history_detail WITH (NOLOCK);

The retention setting for each configuration, which is what should have prevented this:

SELECT [primary_id], [primary_database], [backup_retention_period],
       [history_retention_period], [backup_directory], [backup_compression]
  FROM msdb.dbo.log_shipping_primary_databases WITH (NOLOCK);

SELECT [secondary_id], [secondary_database], [restore_delay],
       [restore_all], [restore_mode], [disconnect_users]
  FROM msdb.dbo.log_shipping_secondary_databases WITH (NOLOCK);

SELECT [secondary_id], [primary_server], [primary_database],
       [backup_source_directory], [backup_destination_directory],
       [file_retention_period]
  FROM msdb.dbo.log_shipping_secondary WITH (NOLOCK);

history_retention_period is in minutes. 14420 is the default, about ten days. A value in the hundreds of thousands explains the row count directly.

And any errors in the history, which is the other useful reason to read this table:

SELECT TOP (50) [log_time], [database_name], [session_status], [message]
  FROM msdb.dbo.log_shipping_monitor_history_detail WITH (NOLOCK)
 WHERE [message] LIKE '%error%' OR [message] LIKE '%fail%'
 ORDER BY [log_time] DESC;

How to fix it

Check the health first, then set the retention, then purge what has already accumulated.

1. Deal with any lag or errors found above. A backlog of unrestored log backups is a recoverability problem and takes priority over the table size.

2. Set a sensible retention period. In minutes, applied per primary database:

-- 30 days, in minutes
EXEC msdb.dbo.sp_change_log_shipping_primary_database
     @database = N'YourDatabase',
     @history_retention_period = 43200;

Thirty days is generous for log shipping history, which is only useful for investigating a recent problem.

3. Purge what is already there:

EXEC msdb.dbo.sp_cleanup_log_shipping_history
     @agent_id   = NULL,
     @agent_type = 0;

@agent_type is 0 for backup, 1 for copy and 2 for restore, so run it for each. On a very large history, do it outside the log shipping job schedule so it does not block the jobs.

4. Run it on every server involved. Log shipping history lives on the primary, on each secondary and on the monitor server if you have one configured, and each keeps its own. Cleaning only the primary leaves the other two growing.

5. Clean up the monitor history, which is often the largest of the three:

EXEC msdb.dbo.sp_cleanup_log_shipping_history;

6. Size msdb properly, since it is carrying this alongside backup and job history:

ALTER DATABASE [msdb] MODIFY FILE (NAME = N'MSDBData', SIZE = 2GB, FILEGROWTH = 256MB);
ALTER DATABASE [msdb] MODIFY FILE (NAME = N'MSDBLog',  SIZE = 1GB, FILEGROWTH = 256MB);

7. Reclaim the space afterwards:

USE [msdb];
GO
DBCC SHRINKFILE (N'MSDBData', 0, TRUNCATEONLY);

And set the alert thresholds while you are in the configuration, because a log shipping setup with no threshold alerting is one you only check when somebody asks:

EXEC msdb.dbo.sp_change_log_shipping_primary_database
     @database = N'YourDatabase',
     @threshold_alert_enabled = 1,
     @threshold_alert = 14420;

That makes log shipping tell you when it falls behind, rather than leaving it to a scan.

How long it takes

About an hour across the servers involved, more if the history is very large. The retention change is immediate.

Related reports

Related checks

Frequently asked questions

Does log shipping clean up after itself? Yes, using the history_retention_period on each configuration, which the jobs apply. A large history usually means that value is set very high or the cleanup has been failing.

What units is the retention period in? Minutes. The default of 14420 is about ten days; 43200 is thirty days.

Do I need to do this on the secondary too? Yes, and on the monitor server if you have one. Each keeps its own history, and the monitor history is often the largest.

Will purging the history affect log shipping? No. The history is a record of what happened. The state log shipping uses to know where it is up to is held separately.