0

I run SQL Server 2012.
I have a maintenance job that runs every night. At the end of the maintenance, I do some cleanup and call this:
Begin try Exec msdb.dbo.sysmail_delete_log_sp @logged_before = '20160613'; EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = '20160613'; End try Begin catch -- Do some log error End catch

I put the transaction in a Try/Catch but it doesn't seem to be working. I keep getting this error :
"Error 1205, Severity 13, level 18 : Transaction (Process ID 112) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

If I run the transaction manually, it runs fast and I don't get any errors.
Any ideas why I keep getting this error or how I can catch it so it doesn't fail my job?

|improve this question
  • Is the deadlock graph available? My guess is that you're using mail fairly heavily and there is too much history in there. I've also witnessed this with distributor cleanup on replication which came down to the same issue. – Sean Gallardy Jul 14 '16 at 19:53
  • No it's not available. Is there a way for me to know the history in the mail? My msdb is quite small. I was wondering if maybe some logBackup job would be running at the same time or something like that, would it create a deadlock? – Danielle Paquette-Harvey Jul 14 '16 at 20:47
  • 1
    Without the deadlock graph, it's impossible to say. If this is reproducible you could turn on TF1222 to put deadlocks to the errorlog. If you wanted to see how much mail you have, you can do select count(*) from msdb.dbo.sysmail_event_log and select count(*) from msdb.dbo.sysmail_allitems – Sean Gallardy Jul 14 '16 at 22:20
  • Ok, I just enabled the traceFlag. I'm gonna try to see if I can reproduce the problem outside of maintenance. If not, I'm gonna have to wait until Monday to see results. I did have the error again last night so I should have it tonight too. – Danielle Paquette-Harvey Jul 15 '16 at 12:49
  • I only have 139 rows in sysmail_event_log and 73 in sysmail_allitems – Danielle Paquette-Harvey Jul 15 '16 at 12:50
0

So I didn't got any luck with the deadlock graph. Doesn't seem to appear in my SQL Logs. But, I did find out that another job has been scheduled to run at the same time than my job, and it was taking backups and sending e-mails at the same time that my job was trying to cleanup the mail history. So changing the schedule of the other job got my problem solved!

|improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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