Start a new topic
Answered

SQL Server job fails because of a deadlock

My SQL Server jobs fail with a "Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim." error, how should I investigate ?


Best Answer

You should first check what queries are involved in the deadlock :

According to the result of the appropriate query, you should be able to understand if the deadlock occurs :

  • Because of an external process than xDM (for example, viewing tables in some query tools lock the tables...) => analyze if this other process is expected and fix it by updating it or killing it (spwho to identify the process and kill <processid>).
  • Because of an internal xDM query
    • Check that your Data Location and Repository schemas follow our Installation Guide about the READ_COMMITTED_SNAPSHOT option that should be turned on. This can be verified by running this query : select name, is_read_committed_snapshot_on
      from sys.databases;
    • Check that you don't have defined many different Job Queues for the same entity
    • If you are using Continuous Loads with large datasets, you might hit the fixed bug MDM-10837 (see the release notes). Upgrading to a higher version than 5.2.4 might solve your issue.

Answer

You should first check what queries are involved in the deadlock :

According to the result of the appropriate query, you should be able to understand if the deadlock occurs :

  • Because of an external process than xDM (for example, viewing tables in some query tools lock the tables...) => analyze if this other process is expected and fix it by updating it or killing it (spwho to identify the process and kill <processid>).
  • Because of an internal xDM query
    • Check that your Data Location and Repository schemas follow our Installation Guide about the READ_COMMITTED_SNAPSHOT option that should be turned on. This can be verified by running this query : select name, is_read_committed_snapshot_on
      from sys.databases;
    • Check that you don't have defined many different Job Queues for the same entity
    • If you are using Continuous Loads with large datasets, you might hit the fixed bug MDM-10837 (see the release notes). Upgrading to a higher version than 5.2.4 might solve your issue.

Hi,


When I tried above queries, sp_who2, there was none in blk_by column and other query please see below screenshot-


image


Thanks,

Sneha

Login to post a comment