Transaction Log Shipping Status report

Hello there,

I realise that a good amount of DBAs do not take advantage of the built-in reports in the SQL Server Management Studio (SSMS). I have been using Log Shipping in the production servers for more than 7 years. I have used in-house developer tools to monitor the status of my log shipped databases and SSMS' built-in reports. Every shop can not build their own monitoring tools and some do not have a budget to buy a 3rd party tool; but everyone can benefit from the built-in reports of SSMS!

When it comes to IT generally, monitoring is crucial; with this in mind, to a DBA, monitoring is everything. We manage our systems with monitoring tools. Some need to act proactively and others to react to problems before our managers or users start yelling at us.

Oh , yea, I would write something about monitoring Log Shippings. If yours is a small shop and if you are a small shop probably you will have a primary production server and probably you will configure Log Shipping only on that primary server, then you can use SSMS' Transaction Log Shipping Status report. I wanted to stress out this report because I do not see people write about it on the internet. So I thought this report could be beneficial to junior DBAs or some shops who does not have a DBA or some IT professionals (I call them "all-in-one", no offence!) who has to carry about every IT related stuff in the office.

Here's a screen shot about the report I am talking about:

Transaction Log Shipping Status report

I had to crop the left side of the report because it was too large to fit on my screen, besides I would blur it all that column as they are the database names of one of my production servers.

Anyway, as you can see from the screenshot above, you can see everything you need to know about the status of a database's Log Shipping. How long it's been the last backup has been performed, the threshold and if the alert would be triggered if the threshold was crossed and the details about other fundamental jobs, Copy and Restore of Log Shipping.

Here's how you can open this report:

- Open SSMS and go to Object Explorer,

- Right click on the SQL Server Instance name and select "Reports" and then "Standard Reports"

- You will see "Transaction Log Shipping Status" report at the bottom of the list, click on it and there you go.

You will see much more reports about server monitoring in this chest, you can play with them to learn more.

I hope it helps!

Cheers,

Ekrem Önsoy