Removing log shipping manually takes 3 stored procedure calls executed in the correct order: first clean up the primary server's secondary reference, then delete the primary configuration, then clean up the secondary server. The normal GUI-based removal through SQL Server Management Studio works fine under ideal conditions, but there are real-world scenarios where that option simply isn't available, and you need to know how to do this by hand.

Why Would You Need to Manually Remove Log Shipping?

The most common scenario is a database restore gone wrong. Someone restores a database over the top of a primary or secondary that was configured for log shipping, which immediately breaks the configuration and leaves orphaned metadata scattered across both servers. The SSMS checkbox is gone. The jobs are confused. The monitor server (if you have one) is still expecting jobs that no longer exist.

Other situations where manual removal becomes necessary include:

  • A secondary server has been decommissioned or rebuilt and the primary still holds references to it
  • Log shipping was configured by a previous DBA and the configuration is partially documented or not documented at all
  • SQL Agent jobs have been manually deleted but the system tables still contain log shipping metadata
  • A failed setup attempt left partial configuration in place

In all of these cases, you need to remove log shipping manually by calling the relevant system stored procedures directly. This clears the metadata from the system tables and removes the associated SQL Agent jobs cleanly.

What Happens If You Leave Orphaned Log Shipping Configuration?

Leaving stale log shipping metadata in place causes real problems. SQL Agent jobs referencing databases or servers that no longer exist will fail repeatedly and generate noise in your job history. Monitoring queries against msdb log shipping tables will return misleading data. If you ever try to reconfigure log shipping on the same database, you'll hit errors because the system already thinks a configuration exists.

It's not catastrophic in the short term, but it's the kind of technical debt that causes confusion during incidents and makes troubleshooting harder than it needs to be.

How to Manually Remove Log Shipping: Step by Step

The process involves three stored procedures executed across two servers. Order matters here. Clean up the primary server first, then move to the secondary.

Step 1: Remove the Secondary Reference from the Primary Server

Connect to the primary SQL Server instance and execute sp_delete_log_shipping_primary_secondary. This procedure removes the record of the secondary database from the primary server's msdb tables. It tells the primary that the secondary relationship no longer exists.

EXEC master.dbo.sp_delete_log_shipping_primary_secondary
    @primary_database   = N'AdventureWorks',
    @secondary_server   = N'LogShippingServer',
    @secondary_database = N'LogShipAdventureWorks'
GO

Replace AdventureWorks with your actual primary database name, LogShippingServer with the name of your secondary SQL Server instance, and LogShipAdventureWorks with the name of the database on the secondary server.

If you're not sure what values to use, query the primary server's log shipping tables first:

SELECT
    primary_database,
    secondary_server,
    secondary_database
FROM msdb.dbo.log_shipping_primary_secondaries

This will show you exactly what the primary server believes the configuration looks like.

Step 2: Delete the Primary Database Configuration

Still on the primary server, execute sp_delete_log_shipping_primary_database. This removes the primary database's log shipping configuration from msdb and also deletes the SQL Agent backup job that was created when log shipping was set up.

EXEC master.dbo.sp_delete_log_shipping_primary_database
    @database = N'AdventureWorks'
GO

After this executes successfully, the primary server is clean. The log shipping backup job will be gone from SQL Agent, and the configuration records in msdb.dbo.log_shipping_primary_databases will be removed.

Note that Step 1 must complete successfully before Step 2 will work. If the secondary reference still exists, sp_delete_log_shipping_primary_database will return an error. This is by design, it enforces the correct cleanup order.

Step 3: Clean Up the Secondary Server

Connect to the secondary SQL Server instance and execute sp_delete_log_shipping_secondary_database. This removes the secondary database configuration from msdb on the secondary server and deletes the copy and restore SQL Agent jobs.

EXEC master.dbo.sp_delete_log_shipping_secondary_database
    @secondary_database = N'LogShipAdventureWorks'
GO

Again, use the actual name of the database on the secondary server. If you have multiple secondaries configured, you'll need to run this on each secondary server.

To verify what's configured on the secondary before running the procedure:

SELECT
    secondary_database,
    primary_server,
    primary_database
FROM msdb.dbo.log_shipping_secondary_databases

Optional: Check for a Monitor Server

If your log shipping configuration included a dedicated monitor server, there may be additional metadata to clean up there. The monitor server stores alert job information and history records in its own msdb tables. In most environments, the monitor role is handled by either the primary or secondary server rather than a dedicated third instance, so this step isn't always relevant.

If you do have a separate monitor server, check msdb.dbo.log_shipping_monitor_primary and msdb.dbo.log_shipping_monitor_secondary on that instance and remove records as needed. Microsoft's documentation on log shipping system tables covers the full schema if you need to trace through what's left behind.

Verifying the Cleanup Was Successful

Once you've run all three procedures, verify the cleanup across both servers. On the primary:

SELECT * FROM msdb.dbo.log_shipping_primary_databases
SELECT * FROM msdb.dbo.log_shipping_primary_secondaries

Both queries should return zero rows for the database you removed. On the secondary:

SELECT * FROM msdb.dbo.log_shipping_secondary_databases
SELECT * FROM msdb.dbo.log_shipping_secondary

Again, zero rows confirms the configuration has been fully removed. Also check SQL Agent on both servers to confirm the backup, copy, and restore jobs are gone.

What About the Secondary Database Itself?

The stored procedures clean up the metadata and jobs, but they don't drop the secondary database. The database will remain on the secondary server, typically in a restoring or standby state depending on how log shipping was configured. What you do with it from there depends on your situation. You might restore it with recovery to bring it online, or drop it entirely if it's no longer needed.

Key Takeaways

  • Manually removing log shipping requires 3 stored procedures executed in order: sp_delete_log_shipping_primary_secondary, then sp_delete_log_shipping_primary_database on the primary server, then sp_delete_log_shipping_secondary_database on the secondary server.
  • The most common reason for needing manual removal is a database restore that overwrites a log-shipped database, breaking the configuration and leaving orphaned metadata.
  • Order matters. Attempting to delete the primary configuration before removing the secondary reference will fail.
  • The stored procedures remove SQL Agent jobs automatically. You don't need to delete the backup, copy, and restore jobs manually.
  • Always verify cleanup by querying the relevant msdb log shipping tables on both servers after running the procedures.

If you're dealing with a broken log shipping configuration as part of a larger incident, or you're not confident about the state of your SQL Server HA setup, DBA Services can help. Our SQL Server health checks include a full review of your high availability and disaster recovery configurations, including log shipping, Always On, and mirroring. We identify exactly what's configured, what's broken, and what needs attention before it becomes a problem. Get in touch to find out more.