Deadlock in MSDB

ptrongo

Hey folks,

Help!


New user... everything seems to work fine except that scheduled jobs are erroring out due to a deadlock, problem is, I don't see it.

sample error


BACKUP LOG successfully processed 0 pages in 0.019 seconds (0.000 MB/sec).

---

3/7/2006 10:58:36 AM: SQL Backup process ended.

3/7/2006 10:58:45 AM: Warning 164: Failed to delete backup entries in msdb tables: Transaction (Process ID 483) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


All of my other scheduled jobs run fine.

Help!

Daniel Handley

Hi

Are you running multiple jobs at the same time as if they are all accessing msdb at the same time it could cause the deadlock?

Can you have a look at the locking information and the SQL logs to see what processes are in the deadlock?

Regards
Dan

ptrongo

Hi,

I would agree with you, but this is the only job failing, and it fails repeatedly. It's also the only job I have using SQL Backup so far. I ship logs on 10 database once every five minutes. I've never had an issue. The actual log backups usually take less then a second or two.

Looks like the spid for the backup has locks on EVERYTHING the only other locks in there are a shared lock

The guy thats hung is...

exec msdb..sp_delete_backuphistory '2006-03-02 10:30'

Patrick

Daniel Handley

Hi Patrick

Can you change the options for log deletion to off and check that the deadlock has gone.

You can then manually prune the database history by using
"exec msdb..sp_delete_backuphistory '2006-03-02 10:30' "
during a quite period.
You could make this a scheduled task and use the datatime function to automate the deletion period.

Regards
Dan