It’s surprisingly difficult to get this information in SQL Server. In fact I was quite stuck trying to figure out how to get this information when I realized that the good people over at Brent Ozar Unlimited already do some checking on this for their sp_Blitz tool.
A quick look at the above code showed me that
dbo.sysssispackageswas what I was looking for. Combine this with:
- 1. Some hack-y SQL for the frequency in human readable format, and
- 2. Some even more hack-y SQL to join on the SQL Agent Job name
And we had pretty much the XML for the Maintenance Plan and the SQL Agent Job Schedule that they were against.
Shane has made the code available as well, so check it out if you have any maintenance plans you’re trying to understand and maybe even get away from.