Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Please edit your question & tag to indicate which version you are referring to. –  ThatGraemeGuy Nov 30 '09 at 16:13
add comment

3 Answers

up vote 1 down vote accepted

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%5Fthomson/archive/2009/10/18/collecting-information-about-your-ssis-packages-ssis-nugget.aspx

share|improve this answer
    
Great link! I was really stumped on how to get the info out of an SSIS package but this seems like it will do the trick. –  Sean Howat Dec 29 '09 at 15:07
add comment

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.

share|improve this answer
    
Note: After playing around with this a bit it dawned on me that this will still give you the database names even after you've stopped shrinking them with a maintenance plan. This is because those shrinks are still recorded in the maintenance plan log history. So this is not an ideal solution for keeping your maintenance plans in check over the long term but it's not a bad place to start tackling the problem of getting rid of auto-shrink in your environment. –  Sean Howat Dec 1 '09 at 20:39
add comment

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.

share|improve this answer
1  
Note: This will not find a shrink operation if the job is run through an SSIS package. –  Sean Howat Dec 8 '09 at 21:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.