SQL Server Log Shipping is a powerful tool for maintaining a standby server, but several factors can break the process.

Log Backup Chain Breaks

Directly taking transaction log backups outside the log shipping configuration can break the log chain. This scenario demands a meticulous approach to restore the sequence.

Network Connectivity Issues

Network connectivity issues between the primary and secondary servers can prevent log backups from being copied. Ensure that the network is stable and the servers can communicate properly.

Incompatible Database Recovery Models

Utilization of an inappropriate recovery model. The Full or Bulk-Logged recovery models are prerequisites for log shipping, yet an inadvertent switch to the Simple recovery model can halt the process.

Insufficient Disk Space

Insufficient disk space on the primary or secondary server can cause log shipping to fail. Monitor disk space regularly and allocate enough space for log backups and restores. Use the following T-SQL code to check the available disk space:

SELECT 
    vs.volume_mount_point, 
    vs.logical_volume_name, 
    vs.total_bytes/1024/1024 AS [Total Size (MB)],
    vs.available_bytes/1024/1024 AS [Available Size (MB)]
FROM sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs;

Mismatched SQL Server Versions or Editions

Mismatched SQL Server versions or editions between the primary and secondary servers can lead to compatibility issues. Always ensure that both servers are running the same version and edition of SQL Server.

Incorrect Permissions

Incorrect permissions on the log shipping jobs can prevent them from executing successfully. Grant appropriate permissions to the SQL Server Agent service account on both servers. Use the following T-SQL code to check the job status:

SELECT 
    j.name AS [Job Name],
    jh.run_status AS [Run Status],
    jh.run_date AS [Run Date],
    jh.run_time AS [Run Time]
FROM msdb.dbo.sysjobs j
JOIN msdb.dbo.sysjobhistory jh 
    ON j.job_id = jh.job_id
WHERE j.name LIKE 'LSBackup%' OR j.name LIKE 'LSCopy%' OR j.name LIKE 'LSRestore%'
ORDER BY jh.run_date DESC, jh.run_time DESC;

Improper Job Configuration

Finally, improper configuration of the log shipping jobs, such as incorrect file paths or retention periods, can cause issues. Double-check the job configurations and ensure that they align with your log shipping requirements.

Conclusion

By addressing these factors proactively, you can ensure that your SQL Server Log Shipping setup remains robust and reliable.

Visit the official Microsoft documentation to learn more about Log Shipping in SQL Server.