Hi,

We run maintenance plan created SSIS packages to backup our databases. The
final step is the cleanup of the jobhistory. We see the occasional deadlock
coming from sp_maintplan_delete_log.

Now I know that msdb backup tables have no indexes. We are looking at
implementing Geoff Hiten's suggested indexes
http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx

Will this our deadlocks?

Thanks

Chris

Typically, adding an index will not resolve deadlock problems, though it may
the error get raised faster. :-) However, that might not always be
the case. Do you have more information about the deadlock? Which object is
deadlocked, and what is the other process trying to do? Adding Geoff's
indexes aren't going to hurt anything, so I would suggest just adding them,
and then wait to see if you get the deadlock again. Who knows, maybe it is
the magic pill... but impossible to know without having more information
about the deadlock itself...



"Chris Wood" <anonymous@microsoft.com> wrote in message
news:OaF3pkBkIHA.1168@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> We run maintenance plan created SSIS packages to backup our databases. The
> final step is the cleanup of the jobhistory. We see the occasional
> deadlock coming from sp_maintplan_delete_log.
>
> Now I know that msdb backup tables have no indexes. We are looking at
> implementing Geoff Hiten's suggested indexes
> http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx
>
> Will this our deadlocks?
>
> Thanks
>
> Chris
>

>> We run maintenance plan created SSIS packages to backup our databases.
>> The final step is the cleanup of the jobhistory. We see the occasional
>> deadlock coming from sp_maintplan_delete_log.

Is it possible that two maintenance plans are calling this procedure
at/around the same time? If so, my suggestion would be to create your own
job that deletes ALL maintenance plan logs, say once a day, by passing in
only the @oldest_time parameter. And then set each maintenance plan to not
delete its own logs.

Understanding how the cleanup tasks work now rather than before we created
the jobs makes this a much better idea. We may well have to change our
backups to just do the one cleanup job a day.

Thanks

Chris

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:u5QsnqBkIHA.5820@TK2MSFTNGP04.phx.gbl...
>>> We run maintenance plan created SSIS packages to backup our databases.
>>> The final step is the cleanup of the jobhistory. We see the occasional
>>> deadlock coming from sp_maintplan_delete_log.
>
> Is it possible that two maintenance plans are calling this procedure
> at/around the same time? If so, my suggestion would be to create your own
> job that deletes ALL maintenance plan logs, say once a day, by passing in
> only the @oldest_time parameter. And then set each maintenance plan to
> not delete its own logs.
>

If you can reproduce this easily, I would file a bug on connect. Multiple
maintenance plans should play nice.



"Chris Wood" <anonymous@microsoft.com> wrote in message
news:OGC8ovBkIHA.4244@TK2MSFTNGP06.phx.gbl...
> Understanding how the cleanup tasks work now rather than before we created
> the jobs makes this a much better idea. We may well have to change our
> backups to just do the one cleanup job a day.

I had raised a bug on Connect about the cleanup just running the delete
stored proc without a job name parameter bug 322320, but then Andrew Kelly
corrected me in how attaching a cleanup task to a maintenance plan had no
idea what the job was called so it could add the parameter.

I may look at running the cleanup a number of times a day so it does not
deadlock with other maintenance jobs adding to the jobhistory.

Thanks again.

Chris

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:%23m9FvxBkIHA.5208@TK2MSFTNGP04.phx.gbl...
> If you can reproduce this easily, I would file a bug on connect. Multiple
> maintenance plans should play nice.
>
>
>
> "Chris Wood" <anonymous@microsoft.com> wrote in message
> news:OGC8ovBkIHA.4244@TK2MSFTNGP06.phx.gbl...
>> Understanding how the cleanup tasks work now rather than before we
>> created the jobs makes this a much better idea. We may well have to
>> change our backups to just do the one cleanup job a day.
>
>

I have seen cases where indexes ed greatly with deadlocks even if it
didn't cure them. If you have two processes which have transactions which
can deadlock with each other, then making one or both of these processes run
faster can greatly reduce the chances that they are both running at the same
time and thus at least decrease the number of deadlocks.

Tom

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message
news:OK7$qnBkIHA.1188@TK2MSFTNGP04.phx.gbl...
> Typically, adding an index will not resolve deadlock problems, though it
> may the error get raised faster. :-) However, that might not always
> be the case. Do you have more information about the deadlock? Which
> object is deadlocked, and what is the other process trying to do? Adding
> Geoff's indexes aren't going to hurt anything, so I would suggest just
> adding them, and then wait to see if you get the deadlock again. Who
> knows, maybe it is the magic pill... but impossible to know without having
> more information about the deadlock itself...
>
>
>
> "Chris Wood" <anonymous@microsoft.com> wrote in message
> news:OaF3pkBkIHA.1168@TK2MSFTNGP02.phx.gbl...
>> Hi,
>>
>> We run maintenance plan created SSIS packages to backup our databases.
>> The final step is the cleanup of the jobhistory. We see the occasional
>> deadlock coming from sp_maintplan_delete_log.
>>
>> Now I know that msdb backup tables have no indexes. We are looking at
>> implementing Geoff Hiten's suggested indexes
>> http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx
>>
>> Will this our deadlocks?
>>
>> Thanks
>>
>> Chris
>>
>
>

Try adding in these indexes:

http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net


"Chris Wood" <anonymous@microsoft.com> wrote in message
news:OaF3pkBkIHA.1168@TK2MSFTNGP02.phx.gbl...
> Hi,
>
> We run maintenance plan created SSIS packages to backup our databases. The
> final step is the cleanup of the jobhistory. We see the occasional
> deadlock coming from sp_maintplan_delete_log.
>
> Now I know that msdb backup tables have no indexes. We are looking at
> implementing Geoff Hiten's suggested indexes
> http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx
>
> Will this our deadlocks?
>
> Thanks
>
> Chris
>

Those were the ones I will be adding. I also think I may have discovered the
source of the problem.

You need to be an admin to actually run the Maintenance Cleanup Task system
stored proc. Our security regime meant that I could only run the backup task
using a lower access id so my backup job raises an alert that triggers the
delete job to run. In both jobs I had a Cleanup History task which in both
cases is run by at least an SQLAgentOperator role account. This means that
the Cleanup History Task actually removes all (not just this jobs)
jobhistory, maintenance plan and backup/restore history that is greater than
its retention interval.

So what I have is a backup job runs on schedule, including cleaning up a
number of tables in msdb, followed by a raiserror that triggers an alert
that fires off the Maintenance Cleanup Task job that also cleans up msdb. No
wonder I can get the odd deadlock.

My answer is to add the indexes and remove the extra Cleanup History tasks
and only run these once or twice a day.

This is how SQL2005 admin sucks compared to SQL2000.

Thanks

Chris

"TheSQLGuru" <kgboles@earthlink.net> wrote in message
news:13uo25pcv9qthdd@corp.supernews.com...
> Try adding in these indexes:
>
> http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
>
> "Chris Wood" <anonymous@microsoft.com> wrote in message
> news:OaF3pkBkIHA.1168@TK2MSFTNGP02.phx.gbl...
>> Hi,
>>
>> We run maintenance plan created SSIS packages to backup our databases.
>> The final step is the cleanup of the jobhistory. We see the occasional
>> deadlock coming from sp_maintplan_delete_log.
>>
>> Now I know that msdb backup tables have no indexes. We are looking at
>> implementing Geoff Hiten's suggested indexes
>> http://weblogs.sqlteam.com/geoffh/ar...ce-Tuning.aspx
>>
>> Will this our deadlocks?
>>
>> Thanks
>>
>> Chris
>>
>
>