locked
SPID - Is it active or idle ?? RRS feed

  • Question

  • Hi,

    I have a need to kill some of the SPID's in my process.  Before I proceed to kill the SPID, I want to find out the last time if the SPID executed any TSQL command and if so, what was the statement.  Basically I wanted to know if the SPID is idle and has no activity for the past 30 minutes or so.

    How would compose the TSQL to perhaps query some sys tables or are there any DMV's that I can run to provide me with such information?

    Thank you in advance

    Sydney

     

    Wednesday, March 23, 2011 7:26 PM

Answers

  • SELECT t.text, s.last_batch, s.login_time, s.open_tran, s.nt_username, s.hostname
    FROM sys.sysprocesses s 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
    WHERE s.status <> 'runnable'
    AND DATEDIFF(mi,last_batch, GETDATE()) > 30
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Marked as answer by Kalman Toth Wednesday, March 30, 2011 4:58 AM
    Wednesday, March 23, 2011 7:57 PM

All replies

  • SELECT t.text, s.last_batch, s.login_time, s.open_tran, s.nt_username, s.hostname
    FROM sys.sysprocesses s 
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
    WHERE s.status <> 'runnable'
    AND DATEDIFF(mi,last_batch, GETDATE()) > 30
    

    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    • Marked as answer by Kalman Toth Wednesday, March 30, 2011 4:58 AM
    Wednesday, March 23, 2011 7:57 PM
  •  

    dbp2784,

     

    Thank you for the reply.

    Will this query show me a list of SPIDs which have logged on to SQL Server for more than 30 minutes or these are the SPIDs that have no activities for 30 minutes or more?

     

    Sydney

    Thursday, March 24, 2011 12:25 PM
  • This will tell you all the SPID's that have no activities for 30 min or more.
    Regards, Deven ----------------------------------------- Please vote if you find any of my post helpful.
    Thursday, March 24, 2011 12:58 PM
  • Thank you for the sample.  I will give this a try and see how this works out.
    Thursday, March 24, 2011 7:13 PM