Identifying Blocked and Blocking sessions from SQL

Deadlock does not allow a user to work in the system. Blocking other user actions is a common occurrence in NAV. In this recipe we will identify Blocked and Blocking sessions.

How to do it...

1. Open SQL Server Management Studio.

2. Open a new query window.

3. Execute the following code:

   sp_who

4. The resulting window should be similar to the following screenshot:

   

How it works...

The sp_who command returns a list of all the connections to the server by querying the sys.sysprocesses system table. The column blk will show the spid of the user who is blocking.

There's more...

We can find deadlocks by writing a query on the SQL Server. Let's take a look at the query:

SELECT
SP.[spid] AS [SPID],
CASE WHEN SP.[blocked] > 0 THEN 'Yes' ELSE '' END AS [Blocked],
SP.[blocked] AS [Blocked by SPID],
SP.[nt_username] AS [User ID],
SD.[name] AS [Database],
SP.[waittime],
SP.[status] as [Current Status],
SP.cmd AS [Current Command]
FROM
[master].[dbo].[sysprocesses] AS SP...