One of the clients that I support has a DR (Disaster Recovery) environment set up.
If you want to read a description on DR (and by extension, Log Shipping), then check out this page from Microsoft.
Log Shipping and DR were relatively new processes to me. I knew what the concept meant, but had not worked with a live scenario.
The drive that holds the logs was getting full. Now this could be completely normal as disk space is consumed as a system runs. But I had to be sure.
So, in this post I wanted to include some handy queries for investigating and troubleshooting the Log Shipping process.
USE master; EXEC sys.sp_help_log_shipping_secondary_database @secondary_database = 'YourDatabaseName';
History of restored logs
SELECT s.database_name,s.backup_finish_date,y.physical_device_name FROM msdb..backupset AS s INNER JOIN msdb..backupfile AS f ON f.backup_set_id = s.backup_set_id INNER JOIN msdb..backupmediaset AS m ON s.media_set_id = m.media_set_id INNER JOIN msdb..backupmediafamily AS y ON m.media_set_id = y.media_set_id WHERE (s.database_name = 'MyDatabase') ORDER BY s.backup_finish_date DESC;
I hope these help!