Detecting Blocking and Identifying the Source in Dynamics AX 2009

Detecting Blocking and Identifying the Source in Dynamics AX 2009

When a process attempts to initiate a lock on a resource that has already been locked by a separate process, blocking occurs.  This can cause Microsoft Dynamics AX user screens to freeze for an extended period of time or a batch job to run beyond the normal runtime.

In Microsoft SQL Server 2008, the Activity Monitor is an easy tool that can be used to detect if extended blocking is occurring.  To access the Activity Monitor open SQL Server Management Studio, connect to your SQL instance, right click your instance in the object explorer and select ‘Activity Monitor’.

Once activity monitor is open, click the processes bar to view the SQL processes.  It may take a few seconds for anything to load.  The default refresh interval is 10 seconds but this can be changed by right click the white space in the top right and selecting the refresh interval you desire.  Also, you can pause the refresh if necessary.  This can be useful when trying to filter the processes.

Once the processes load, you will want to look at the ‘Wait Type’ column, ‘Blocked By’ column, and ‘Head Blocker’ columns.  The Session Id’s with ‘LCK’ wait types are the processes that are stuck and waiting for another process to release a lock on a resource.  The Session Id with a 1 in the ‘Head Blocker’ column is the process that is holding a lock and preventing another process from accessing the same resource.

By right clicking the Head Blocker and selecting ‘View Details’ you can get an idea of the process that is running.  The statement shown is the current statement being executed and is not always the statement that is holding the lock.  This info can be used to help identify the overall process/activity.

Once you know the SQL Session Id (SPID) you can try to identify the user/process associated to the SPID.  In AX, go to Administration -> Common forms -> Online Users.  Here you can use the SPID column on the form to gather a lot of useful information.  You can identify the AOS instance, the computer the SPID originated on, the status, session type, and login date/time.  All of this information can be used to help identify the process causing the blocking.

TIP:  In an environment with multiple AOS’s, you will only see the SPID’s for the AOS your current AX client session is connected to.  You can use the ‘Host Name’ column in SQL Activity Monitor to identify what AOS the blocking SPID is on.

Share on Google+Tweet about this on TwitterShare on LinkedInShare on FacebookEmail this to someone