Hi, For example I have 10 SPID return blocking in the database, with this 10 SPID how could I get the respective SQL Script running which causing the block process. How can I write a select statement to return the SPID and SQL Script. Please advice with code sample. TQ. rgds/sm
Information of all running process are available in sys.sysprocesses run the following query : SELECT sp.spid , sp.blocked AS BlockingProcess , DB_NAME(sp.dbid) AS DatabaseName , sp.loginame , CAST(text AS VARCHAR(1000)) AS SqlStatement FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle)
Hi I have the below script schedule auto run by server to return the SPID when there is a blocking process, please advice how to build the SQL Script return by the blocking SPID.Select spid, hostname, [Program_name], loginame, login_time, SQLScript (?)from sysprocesses (nolock) where blocked = 0 and spid in ( select blocked from sysprocesses (nolock) where blocked <> 0)
SELECT sp.spid , sp.hostname , sp.program_name , sp.loginame ,sp.login_time , CAST(text AS VARCHAR(1000)) AS SQLScript FROM sys.sysprocesses sp CROSS APPLY sys.dm_exec_sql_text (sp.sql_handle) where sp.blocked <>0