 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Deadlocks in msdb on SQL2005
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/archive/2008/01/21/MSDB-Performance-Tuning.aspx Will this help our deadlocks? Thanks Chris Typically, adding an index will not resolve deadlock problems, though it may help 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... > Hi, > [quoted text clipped - 11 lines] > > 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 >>> We run maintenance plan created SSIS packages to backup our databases. >>> The final step is the cleanup of the jobhistory. We see the occasional [quoted text clipped - 5 lines] > 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. > 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 > If you can reproduce this easily, I would file a bug on connect. Multiple > maintenance plans should play nice. > >> 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 helped 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 > Typically, adding an index will not resolve deadlock problems, though it > may help the error get raised faster. :-) However, that might not always [quoted text clipped - 20 lines] >> >> Chris Try adding in these indexes: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx  SignatureKevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net > Hi, > [quoted text clipped - 11 lines] > > 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 > Try adding in these indexes: > [quoted text clipped - 15 lines] >> >> Chris |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.