I know you can use a query to find out which databases have auto-shrink enabled but I'm wondering if this can be done for maintenance plans. Right now we only have a few maintenance plans so it's easy enough to check manually but I can see how it would be useful to have a query to do this.
I think here is what you want. Jamie Thompson has posted on how to extract information from the SSIS packages. Maintenance plans are created as SSIS packages and you can use the information in this post to achieve what you want. Good Luck!
I may have found a way to make this work in my environment. Our maintenance plans were created with the drag and drop system that SQL Server 2008 uses so this may not work the same if you don't use that system. Here's the query I used:
SELECT DISTINCT line3 FROM sysmaintplan_logdetail
I realize the line1-3 columns may look a little confusing so I'll try to explain. The sysmaintplan_logdetail table has a column for line1-5. These 5 lines seem to describe what is going on with a particular task in a maintenance plan. They are not always full and I'm not sure if SQL Server would ever need to create more than the 5 lines but that's what was there for me.
When I looked at the entries in my sysmaintplan_logdetail table I noticed that line 1 and 2 both contained the word shrink any time a shrink operation was done in a maintenance plan. The specific lines were:
line1 - Shrink Database Task(SERVER NAME)
I can't guarantee that these entries would be in the same location or even the same string on another server but it does seem possible to create a simple query using the sysmaintplan_logdetail after looking at it for a few minutes. Hope this helps someone else as well.
The msdb.sysjobsteps table has a column called command. This command column contains the SQL statement to be run by a particular step from a job. The following query should determine if any of the job steps shrink the database:
This should determine if there are any jobs on the server that use Shrink and supply you with the job name.