June 27, 2011 at 12:02 pm
I have been googling this question and have not been very successful in finding an answer.
Some quick background: We have an application that heavily relies on Linked Servers (currently looking into replicating some of this data), but what happens, is that alot of times, this application times out on the webserver but does not kill it's connection to the database. Then the end user fires up another browser, attempts to do the same thing in the application, and gets the same result.
When application times out, the SQL Process stays and continues to eat on CPU Time and Disk IO.
Is there anyway to stop or kill the SQL Process once the application times out.
Thanks in Advance for the help...
June 27, 2011 at 12:20 pm
I have just ran into the exact same problem last week and this is what I came up with to auto-kill long running queries. I have not implemented this as I am very hesitant to automatically kill any spids so it has been modified to only notify me of long running spids so I can manually investigate and kill if necessary.
declare @spid varchar(3)
declare @sql nvarchar(max)
declare @query varchar(max)
declare @individualquery varchar(max)
declare @body varchar(max)
declare @subject varchar(max)
DECLARE c_longrunning CURSOR LOCAL FOR SELECT s.session_id AS spid
,t.[text] AS lastSQLText
, SUBSTRING (t.text,
r.statement_start_offset/2,
(CASE WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), t.text)) * 2
ELSE r.statement_end_offset END -
r.statement_start_offset)/2) as IndividualQuery
FROM sys.dm_exec_sessions AS s
LEFT JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
LEFT JOIN sys.dm_exec_connections AS c ON c.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE s.is_user_process = 1
and s.session_id <> @@spid
and s.login_name = '<username>'
and s.[status] = 'running'
and s.last_request_end_time < dateadd(mi,-20,getdate())
OPEN c_longrunning
WHILE (1=1)
BEGIN
FETCH NEXT FROM c_longrunning
INTO @spid,@query,@individualquery
IF @@FETCH_STATUS < 0
BREAK
SET @body = 'This Spid has been killed due to following query(s): '+@spid+'
'+@query+'
'+@individualquery+''
set @subject = 'Killed spid '+@spid+''
EXEC msdb.dbo.sp_send_dbmail @recipients = '<recipient>',
@subject = @subject,
@body = @body,
@profile_name = '<profile>',
@importance = 'High'
set @sql = 'kill '+@spid
--print @sql
exec sp_executesql @sql
END
CLOSE c_longrunning
DEALLOCATE c_longrunning
June 27, 2011 at 12:28 pm
Bob, Thanks soo much for the query, this will definitely come into handy...
From the application side of it, when the application times out is there any way to automatically kill the SQL process? I guess this might be more of a developer type question...
Thanks Again.
June 27, 2011 at 12:48 pm
I briefly looked into this and talked to our developers and it would very much depend on how the application was creating the connection it the first place and, when timing out, how it was closing the connection.
In my case it was a .net application that would timeout then use the close method. There is also a dispose method which, if I understood correctly, would completely breakdown the connection but then you could not make use of connection pooling. Another option was to use the cancel method of the sqlcommand class.
June 27, 2011 at 1:31 pm
Robert klimes (6/27/2011)
There is also a dispose method which, if I understood correctly, would completely breakdown the connection but then you could not make use of connection pooling.
This is a common (and often disastrous) misconception. The dispose method on a sql connection object removes the connection object from .net and returns the connection to the pool. This connection will remain in the pool until it either expires or is needed again. The close method does NOT return the connection to the pool and you can very quickly max out your available connections. You should always dispose of your .net sql connection to maximize your connection pooling. It is typically "best practice" to wrap all data calls inside of a "using" block. This block will work for any object that inherits IDisposable. At the end of the block it will dispose of the object (with or without exception handling).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply