Home / server / Questions / 89461
Accepted
Sean Howat
Asked: 2009-12-01 08:02:44 +0800 CST

Is there a query to check which maintenance plans have shrink enabled in SQL Server 2008?

  • 772

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.

sql-server-2008

3 Answers

  1. 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
    WHERE line1 LIKE '%Shrink%' OR line2 LIKE '%Shrink%'

    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)
    line2 - Shrink Database on Local server connection
    line3 - Contained the database 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.

    • 1

  2. 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:

    SELECT name
    FROM sysjobs INNER JOIN sysjobsteps
    ON sysjobs.job_id = sysjobsteps.job_id
    WHERE command LIKE '%Shrink%'

    This should determine if there are any jobs on the server that use Shrink and supply you with the job name.

    • 1
  3. Best Answer

    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!

    http://sqlblog.com/blogs/jamie_thomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx

    • 1