Block Detection & Resolution Queries
  

---

Here are the script that will allow you to check if there are any blocks in your database server. 

Below two queries are respectively for memory status & history of query execution:

select* FROM sys.dm_exec_query_memory_grants

GO

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]

FROMsys.dm_exec_query_statsAS deqs

CROSSAPPLY sys.dm_exec_sql_text(deqs.sql_handle)AS dest

ORDERBY deqs.last_execution_timeDESC

GO

Below query gives current SPID:

SELECT @@SPID

GO

Below two SPs give current status of all processes running on the server:

sp_who

GO

sp_who2

GO

Below queries give specific info about blocks, change @@SPID with the SPID of your choice:

SELECT* FROM sys.dm_exec_requests

WHERE blocking_session_id <> 0;

GO

select* from sys.sysprocesses where blocked = 1

GO

SELECT session_id, wait_duration_ms, wait_type, blocking_session_id FROM sys.dm_os_waiting_tasks

WHERE blocking_session_id <> 0

GO

DBCC INPUTBUFFER(@@SPID)

GO

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext= sql_handle

FROM sys.sysprocesses

WHERE spid= @@SPID

SELECT TEXT

FROM sys.dm_exec_sql_text(@sqltext)

GO

DECLARE @sqltext VARBINARY(128)

SELECT @sqltext = sql_handle

FROM sys.sysprocesses

WHERE spid= @@SPID

SELECT TEXT

FROM ::fn_get_sql(@sqltext)

GO

SELECT p.spid

,convert(char(12), d.name) db_name

,program_name

,convert(char(12), l.name) login_name

,convert(char(12), hostname) hostname

, cmd

, p.status

, p.blocked

, login_time

, last_batch

, p.spid

FROM master..sysprocesses p

JOIN master..sysdatabases d ON p.dbid = d.dbid

JOIN master..syslogins l ON p.sid = l.sid

WHERE p.blocked= 0

AND EXISTS ( SELECT 1

FROM master..sysprocesses p2

WHERE p2.blocked = p.spid )

GO

Below query to kill a SPID, change @@SPID with the SPID of your choice but please remember that the blocking should get resolved by itself. The other processes have to just wait until the first process completes. If the first process never completes you would have to kill that process in order for the other processes to continue:

KILL @@SPID

GO 

---