So you have setup log shipping between a primary and a secondary server. Everything went smoothly and the secondary DB is being updated with transaction logs. Now what? I.e. how do you monitor the status of the log shipping? What is the throughput? What is the current status of the log shipping backup/copy/restore?
I have researched and collected a set of commands that helps me with finding this information. They were tested on SQL 2005.
Task: First I need to know how many DBs on this server are being log shipped?
select * from log_shipping_monitor_primary
The primary_database column will give you the names of the DBs. The last_backup_file column will show you the location to where the transaction logs are being backed up to. The last_backup_date will show you the date of the last log file that was backed up. This date will come in handy as you can find out if you have any old log shipping jobs that you do not need anymore. I can then go to the last_backup_file location and possibly clear up a lot of space.
Task: Where are the secondary servers to these primary DBs?
select * from log_shipping_primary_secondaries
The secondary_server column will give you the name of the server(s) to which your logs are being shipped to.
Task: How has log shipping being doing so far?
select * from log_shipping_monitor_history_detail
The agent_type column stands for: 0 = Backup,1 = Copy, 2 = Restore. The session_status column stands for: 0 = Starting, 1 = Running, 2 = Success, 3 = Error, 4 = Warning (so look for 3 and 4). The message column will give you some detail on what the issue is.
Task: Are there any current issues with the log shipping?
select * from log_shipping_monitor_error_detail
This table will return a list of any errors associated with log shipping. You can filter by the database_name if you are concerned with any DB in particular. I usually order using log_time desc. Once again, the message column will give some helpful information.
Armed with these queries, you will be able to take over a new server and get up to speed with the log shipping setup on that server. I have referenced the system tables that related to log shipping. There are SPs that you can use which will be more dynamic across the different versions of SQL. My next step will be to measure log shipping performance/ throughput which I will blog about later.
The BOL link containing detailed information about log shipping monitoring is here.