Investigating a Log Shipping process

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.

Monitor

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!

Subscribe

0 comments