avatar image
Former Member

Dispatchers Deadlock Issue

Hi Everyone,

We are frequently getting deadlock issues where all the dispatchers gets hanged and nothing executes in the system. The job log, system logs all shows basic information as some thing like "SQL Exception: Transaction (process ID xxx) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

After few mins the logs section shows the information as lock table overflow message.

Even double clicking on the above messages in the logs sections not opens any other window to show detailed info.

Currently we are using IDM 7.2 SP8 Patch on MS SQL DB.

Can any one kindly suggest what is the best way of handling these situations and troubleshooting methods to find the detailed root cause of this type of issues?

Regards,

Venkata Bavirisetty

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Jul 29, 2014 at 09:05 AM

    Hi,

    We have an updated version of the procedure mc_chk_sema_timeout that tries to solve the issue but need to have it verified. I still haven't been able to reproduce the issue locally. If you see this procedure listed/hanging when running:

    SELECT database_id,st.text, r.session_id, r.status, r.command, r.cpu_time,

    r.total_elapsed_time

    FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st

    order by database_id 

    please contact support so we can provide you with the patched proc to see if it helps.

    Br,

    Chris

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 28, 2014 at 11:51 AM

    Venkata,

    Please describe your dispatchers, what server they are on and what they do.

    In general you should have ONE dispatcher per physical host. Multiple dispatchers running on the same host, running the same jobs can cause deadlocks.

    Also I'd suggest reviewing some of Former Member's blogs on Database queries.  They might help as well.

    Matt

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jul 28, 2014 at 07:40 AM

    Hi Venkata,

    Please activate the deadlock trace.

    As SA run : DBCC TRACEON (1222,-1)

    This will create .trc files in the SQL Server log directory.

    In the file look for processes (waiter, owner) that might give you a clue what is causing the issue.

    The trace can be switched of by DBCC TRACEOFF (1222,-1)  or when the db is restarted.

    Kind regards, Ineke

    Add comment
    10|10000 characters needed characters exceeded

Skip to Content