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:
Exec msdb.dbo.sysmail_delete_log_sp @logged_before = '20160613';
EXECUTE msdb.dbo.sysmail_delete_mailitems_sp @sent_before = '20160613';
-- Do some log error
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?