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
>