SQL stored procedure (Microsoft SQL Server 2008R2, may also work on 2008 or 2005, but no promises) to find and kill requests that are stuck in the ASYNC_NETWORK_IO state for long periods of time. This is typically caused by malfunctioning client software but can also be caused by network problems. Add the procedure to the master database, and se…

gistfile1.sql

create procedure sp_terminate_stuck_network as

declare CRSR cursor for
	select session_id from sys.dm_exec_requests
		where wait_type = 'ASYNC_NETWORK_IO'
		and wait_time > 5*60*1000 -- 5 minutes;

open CRSR;

declare @SESSION as int
declare @sql as nvarchar(255)
declare @emailtext as nvarchar(255)

fetch next from CRSR into @SESSION
while @@FETCH_STATUS = 0
begin
	set @sql = 'kill ' + @SESSION
	set @emailtext = 'SQL session ' + @SESSION + ' killed due to long async IO wait'
	execute @sql
	exec msdb.dbo.sp_send_dbmail 
		@profile_name = '<database mail profile name>',
		@recipients = '<email addresses to send to>',
		@body = @emailtext,
		@subject = 'SQL Server session kill notification';
end

close CRSR;