There are numerous ways you can view blocking information but I’ll share the most popular in sp_who and sp_who2.
Like their name implies sp_who and sp_who2 give information about “who” is in your SQL Server as well as other useful information including processes and blocking info.
Below is an example of sp_who running against a SQL Server:
As you can see, sp_who will return SPID, status, loginname, blk, dbname, etc.
spid – The system process ID.
ecid – The execution context ID of a given thread associated with a specific SPID.
status – The process status.
loginame – The login name associated with the particular process.
hostname – The host or computer name for each process.
blk – The system process ID for the blocking process, if one exists. Otherwise, this column is zero.
When a transaction associated with a given SPID is blocked by an orphan distributed transaction, this column will return a ‘-2′ for the blocking orphan transaction.
dbname – The database used by the process.
cmd – The SQL Server command (Transact-SQL statement, SQL Server internal engine process, and so on) executing for the process.