none
suspended process

    Question

  • Hi

    sql server 2005 server

    I am new to sqlserver.  I am trying to write a plugin to monitor a sqlserver 2005 instance for suspended processes. Could you help me in providing the query or tell me what is the best way for this please?

    Thanks

    AP

    • Moved by Tom PhillipsModerator Monday, April 26, 2010 1:16 PM TSQL Question (From:SQL Server Database Engine)
    Monday, April 26, 2010 10:59 AM

Answers

  • Hi,

    Given that you are using SQL Server 2005, rather than using the sys.processes system table I would strongly recommend that you use the SQL Server Dynamic Management Views (DMV).

    In particular, you will want to look at the sys.dm_exex...... family of DMV's such as:


    Also, keep in mind that just because a given process is in a suspended state does not necessarily mean that there is an issue. It is part of the natural flow of processes for them to become SUSPENDED say when they are waiting on a system resource to respond such as the network or disk subsystem.


    I hope this helps but please feel free to pose any further questions you may have.

     


    John Sansom | SQL Server Consultant with Santech Solutions
    • Proposed as answer by Naomi NModerator Monday, April 26, 2010 1:30 PM
    • Marked as answer by KJian_ Friday, April 30, 2010 7:33 AM
    Monday, April 26, 2010 12:24 PM
  • What you are describing is watching for "long running queries".  There are many ways to do this also. 

    I use the following stored proc and run it every hour to check for anything strange running more than 120 mins (adjust your time as needed).

     

     

    ALTER PROC [dbo].[up_EmailLongQueries]
    AS
    SET NOCOUNT ON
    
    DECLARE @emaildba varchar(100), @subject varchar(256), @body varchar(8000)
    
    SET @emaildba = 'emailaddress@address.com'
    
    
    DECLARE @servername varchar(256), @loginid varchar(256), @ntdomain varchar(100), @ntusername varchar(100),
    	@hostname varchar(255), @hostproc varchar(10), @spid varchar(10), @execcont varchar(10), @waittype varchar(256),
    	@waitresource varchar(255), @waittime varchar(10), @blockedby varchar(10), @starttime datetime, @runtimesecs varchar(10),
    	@runtimemin varchar(10), @status varchar(20), @dbname varchar(255), @commandtype varchar(25), @sqllen int, @sqlcmd varchar(max)
    
    DECLARE longquery CURSOR FOR
    	select 
    	@@SERVERNAME as ServerName,
    	sproc.loginame LoginID,
    	sproc.nt_domain AS NTDomain,
    	sproc.nt_username AS NTUserName,
    	sproc.Hostname HostName,
    	sproc.hostprocess,
    	sproc.spid Session_ID,
    	sproc.ecid Execution_Context,
    	sproc.lastwaittype AS Wait_Type, 
    	sproc.waitresource AS Wait_Resource,
    	sproc.waittime AS Wait_Time, 
    	CASE WHEN sproc.blocked = 0 THEN 0 ELSE sproc.blocked END as BlockedBy,
    	sproc.last_batch Started_At,
    	datediff(second,sproc.last_batch,getdate()) Elapsed_Seconds,
    	datediff(mi,sproc.last_batch,getdate()) Elapsed_Mins,
    	sproc.status Status, 
    	DB_NAME(sproc.dbid) AS DBName,
    	sproc.cmd Command,
    	len(sqltext.TEXT) SQL_Length,
    	SUBSTRING(sqltext.text, (sproc.stmt_start/2)+1, 
      ((CASE sproc.stmt_end
       WHEN -1 THEN DATALENGTH(sqltext.text)
       ELSE sproc.stmt_end
       END - sproc.stmt_start)/2) + 1) AS Query_SQL
    	--,sproc.*
    	from master.sys.sysprocesses sproc
    	OUTER APPLY master.sys.dm_exec_sql_text(sproc.sql_handle) AS sqltext
    	where sproc.spid <> @@SPID
    	AND sproc.spid > 50
    	AND sproc.cmd <> 'AWAITING COMMAND'
    	AND datediff(mi,sproc.last_batch,getdate()) > 120 -- Running longer than 2 hours
    	ORDER BY sproc.spid, sproc.ecid
    
    
    OPEN longquery
    FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
    		@hostname, @hostproc, @spid, @execcont, @waittype,
    		@waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
    		@runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	SET @subject = 'Warning: ' + @servername + ' SPID: ' + @spid + ' process running for ' + @runtimemin + ' Mins'
    	SET @body = 'Server: ' + @servername + CHAR(10) 
    	SET @body = @body + 'SPID: ' + @spid + CHAR(10) 
    	SET @body = @body + 'Start: ' + CONVERT(varchar(25), @starttime, 120) + ' Running for: ' + @runtimemin + ' Mins' + CHAR(10) 
    	SET @body = @body + 'Username: ' + @loginid + CHAR(10) 
    	SET @body = @body + 'Database: ' + @dbname + CHAR(10) 
    	SET @body = @body + 'Wait: ' + RTRIM(@waittype) + ' ' + @waittime + 'ms ' + CASE WHEN @blockedby <> 0 THEN 'Blocked By SPID: ' + @blockedby ELSE '' END+ CHAR(10) 
    	SET @body = @body + 'From Host: ' + RTRIM(@hostname) + ' PID: ' + @hostproc + CHAR(10) 
    	SET @body = @body + CHAR(10) + 'Query: ' + LEFT(@sqlcmd,100) + CHAR(10) 
    
    	SET @body = @body + CHAR(10) + CHAR(10) + '******* THIS IS AN UNMONITORED MAILBOX. DO NOT REPLY TO THIS EMAIL ******* ' + CHAR(10) 
    
    	--PRINT @subject
    	--PRINT @body
    
    	-- Send email
    	EXEC msdb.dbo.sp_send_dbmail @recipients = @emaildba, @subject=@subject, @body=@body, @body_format = 'TEXT'
    
    
    	FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
    		@hostname, @hostproc, @spid, @execcont, @waittype,
    		@waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
    		@runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd
    END
    CLOSE longquery
    DEALLOCATE longquery
    
    SET NOCOUNT OFF
    

     

    • Marked as answer by KJian_ Friday, April 30, 2010 7:33 AM
    Monday, April 26, 2010 7:50 PM
  • In addition to the previous replies, take a look at this blog Performance (How is my SQL Server running?)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, April 30, 2010 7:33 AM
    Monday, April 26, 2010 7:56 PM

All replies

  • Can you please provide more details on what you are looking for? You can use sp_who2 to find out suspended processes, please note that the spids between 1 and 50 indicate system processes which you needn't bother.
    Monday, April 26, 2010 11:31 AM
  • I need to find the suspended processes which is not system related and are waiting for long time and blocking other sessions. I need the sql for the query to run that as script to monitor the db. Mainly I need the database view where I can query the process related information?
    Monday, April 26, 2010 11:37 AM
  • try sys.sysprocesses, there you also have the wait_time column and the blocked column which holds the blocking session_id

    user processes have a session_id > 50


    Bodo Michael Danitz - MCT, MCITP - free consultant - performance guru - www.sql-server.de

    Monday, April 26, 2010 11:50 AM
  • Hi,

    Given that you are using SQL Server 2005, rather than using the sys.processes system table I would strongly recommend that you use the SQL Server Dynamic Management Views (DMV).

    In particular, you will want to look at the sys.dm_exex...... family of DMV's such as:


    Also, keep in mind that just because a given process is in a suspended state does not necessarily mean that there is an issue. It is part of the natural flow of processes for them to become SUSPENDED say when they are waiting on a system resource to respond such as the network or disk subsystem.


    I hope this helps but please feel free to pose any further questions you may have.

     


    John Sansom | SQL Server Consultant with Santech Solutions
    • Proposed as answer by Naomi NModerator Monday, April 26, 2010 1:30 PM
    • Marked as answer by KJian_ Friday, April 30, 2010 7:33 AM
    Monday, April 26, 2010 12:24 PM
  • You want to look for "blocked" processes, not suspended processes.  There are many reasons for a process to be "suspended".

    There are many, many ways to monitor "blocked" processes.

    In additon to the ones mentioned, see:

     

    http://blogs.technet.com/rob/archive/2008/05/26/detecting-sql-server-2005-blocking.aspx

     

    Monday, April 26, 2010 1:25 PM
  • Thanks for your replies, very helpful. Actually the reason why we need to monitor suspended process is that recently there were lot of sessions went to suspended mode and was waiting for long time as one of the indexes was dropped by a user. so we need to monitor the db to complain to us when the sessions goes to  suspended mode  and wait is over a period of time. Is it a good idea? Please advice

     

    Thanks

    Monday, April 26, 2010 1:28 PM
  • Hi, I've made a script to monitor blocked process: 

    use master
    GO
    declare @spid smallint

    select * from sysprocesses (nolock) where blocked = 0 and spid in (
    select blocked from sysprocesses (nolock) where blocked <> 0 )

     

    DECLARE c_curs CURSOR FOR
    select spid from sysprocesses (nolock) where blocked = 0 and spid in (
    select blocked from sysprocesses (nolock) where blocked <> 0
    )

     

    OPEN c_curs
    FETCH NEXT FROM c_curs
    INTO @spid

    WHILE @@FETCH_STATUS = 0
    BEGIN

    dbcc inputbuffer(@spid)

    FETCH NEXT FROM c_curs
    INTO @spid

    END --while

     

    CLOSE c_curs
    DEALLOCATE c_curs

    The script show also the query that is executing the blocked process. The documentation I've read to write the script is:

    http://www.sqlservercentral.com/Forums/Topic365627-146-1.aspx

    http://www.kodyaz.com/articles/identify-kill-blocking-sql-server-processes.aspx

    Hope it helps

    Nicola

    Monday, April 26, 2010 1:48 PM
  • What you are describing is watching for "long running queries".  There are many ways to do this also. 

    I use the following stored proc and run it every hour to check for anything strange running more than 120 mins (adjust your time as needed).

     

     

    ALTER PROC [dbo].[up_EmailLongQueries]
    AS
    SET NOCOUNT ON
    
    DECLARE @emaildba varchar(100), @subject varchar(256), @body varchar(8000)
    
    SET @emaildba = 'emailaddress@address.com'
    
    
    DECLARE @servername varchar(256), @loginid varchar(256), @ntdomain varchar(100), @ntusername varchar(100),
    	@hostname varchar(255), @hostproc varchar(10), @spid varchar(10), @execcont varchar(10), @waittype varchar(256),
    	@waitresource varchar(255), @waittime varchar(10), @blockedby varchar(10), @starttime datetime, @runtimesecs varchar(10),
    	@runtimemin varchar(10), @status varchar(20), @dbname varchar(255), @commandtype varchar(25), @sqllen int, @sqlcmd varchar(max)
    
    DECLARE longquery CURSOR FOR
    	select 
    	@@SERVERNAME as ServerName,
    	sproc.loginame LoginID,
    	sproc.nt_domain AS NTDomain,
    	sproc.nt_username AS NTUserName,
    	sproc.Hostname HostName,
    	sproc.hostprocess,
    	sproc.spid Session_ID,
    	sproc.ecid Execution_Context,
    	sproc.lastwaittype AS Wait_Type, 
    	sproc.waitresource AS Wait_Resource,
    	sproc.waittime AS Wait_Time, 
    	CASE WHEN sproc.blocked = 0 THEN 0 ELSE sproc.blocked END as BlockedBy,
    	sproc.last_batch Started_At,
    	datediff(second,sproc.last_batch,getdate()) Elapsed_Seconds,
    	datediff(mi,sproc.last_batch,getdate()) Elapsed_Mins,
    	sproc.status Status, 
    	DB_NAME(sproc.dbid) AS DBName,
    	sproc.cmd Command,
    	len(sqltext.TEXT) SQL_Length,
    	SUBSTRING(sqltext.text, (sproc.stmt_start/2)+1, 
      ((CASE sproc.stmt_end
       WHEN -1 THEN DATALENGTH(sqltext.text)
       ELSE sproc.stmt_end
       END - sproc.stmt_start)/2) + 1) AS Query_SQL
    	--,sproc.*
    	from master.sys.sysprocesses sproc
    	OUTER APPLY master.sys.dm_exec_sql_text(sproc.sql_handle) AS sqltext
    	where sproc.spid <> @@SPID
    	AND sproc.spid > 50
    	AND sproc.cmd <> 'AWAITING COMMAND'
    	AND datediff(mi,sproc.last_batch,getdate()) > 120 -- Running longer than 2 hours
    	ORDER BY sproc.spid, sproc.ecid
    
    
    OPEN longquery
    FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
    		@hostname, @hostproc, @spid, @execcont, @waittype,
    		@waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
    		@runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd
    
    WHILE @@FETCH_STATUS = 0
    BEGIN
    	SET @subject = 'Warning: ' + @servername + ' SPID: ' + @spid + ' process running for ' + @runtimemin + ' Mins'
    	SET @body = 'Server: ' + @servername + CHAR(10) 
    	SET @body = @body + 'SPID: ' + @spid + CHAR(10) 
    	SET @body = @body + 'Start: ' + CONVERT(varchar(25), @starttime, 120) + ' Running for: ' + @runtimemin + ' Mins' + CHAR(10) 
    	SET @body = @body + 'Username: ' + @loginid + CHAR(10) 
    	SET @body = @body + 'Database: ' + @dbname + CHAR(10) 
    	SET @body = @body + 'Wait: ' + RTRIM(@waittype) + ' ' + @waittime + 'ms ' + CASE WHEN @blockedby <> 0 THEN 'Blocked By SPID: ' + @blockedby ELSE '' END+ CHAR(10) 
    	SET @body = @body + 'From Host: ' + RTRIM(@hostname) + ' PID: ' + @hostproc + CHAR(10) 
    	SET @body = @body + CHAR(10) + 'Query: ' + LEFT(@sqlcmd,100) + CHAR(10) 
    
    	SET @body = @body + CHAR(10) + CHAR(10) + '******* THIS IS AN UNMONITORED MAILBOX. DO NOT REPLY TO THIS EMAIL ******* ' + CHAR(10) 
    
    	--PRINT @subject
    	--PRINT @body
    
    	-- Send email
    	EXEC msdb.dbo.sp_send_dbmail @recipients = @emaildba, @subject=@subject, @body=@body, @body_format = 'TEXT'
    
    
    	FETCH NEXT FROM longquery INTO @servername, @loginid, @ntdomain, @ntusername,
    		@hostname, @hostproc, @spid, @execcont, @waittype,
    		@waitresource, @waittime, @blockedby, @starttime, @runtimesecs,
    		@runtimemin, @status, @dbname, @commandtype, @sqllen, @sqlcmd
    END
    CLOSE longquery
    DEALLOCATE longquery
    
    SET NOCOUNT OFF
    

     

    • Marked as answer by KJian_ Friday, April 30, 2010 7:33 AM
    Monday, April 26, 2010 7:50 PM
  • In addition to the previous replies, take a look at this blog Performance (How is my SQL Server running?)
    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by KJian_ Friday, April 30, 2010 7:33 AM
    Monday, April 26, 2010 7:56 PM