SQL Server Performance

How To Get a SQL Script with SPID

Discussion in 'SQL Server 2008 General Developer Questions' started by seemun, Mar 10, 2010.

  1. seemun New Member

    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
  2. moh_hassan20 New Member

    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)
  3. seemun New Member

    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)
  4. moh_hassan20 New Member

    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

Share This Page