Is there any way to kill all open SQL Server connections in an ASP.NET code behind? Why do I want to do this ? Because some team developers forget to close their connections and I am sick of reminding them.
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlDataAdapter adapter = new SqlDataAdapter();
SqlCommand SqlCommand = new SqlCommand();
connection.Open();
Killing all open connections would be very bad: you don't know what those connections are doing in multi-threaded code like asp.net. To answer directly: no that isn't possible unless you manually track all the connections - but any time spent tracking them would be better spent fixing the underlying problem.
The most appropriate option here is to add using statements around your use of connections.
I agree that it should be done at root cause, so the C# code must properly treat them but in case you risk to degrade an active production server like it happened in my case.
I figured out that I could use a SQL script to close all the connections in Idle since more than 200 minutes with basically no impact / issues on the badly designed software.
Rather than calling it from a program I put the code below in a STEP of a SQL Server Agent JOB, so it is run on regular basis from the SQL server itself.
In my case I wanted to be sure to not delete some connections, i.e. coming from well designed software or from the SQL Server agent itself. All these "NEVER TOUCH" connections are identified by where condition after
DELETE FROM #Who2
Eventually once you set applicationName properly on the connectionstring by using ProgramName NOT IN ('YOURAPPNAME') in that delete you can easily exclude from killing all the connections except the ones coming from your C# application
DECLARE @Now DATETIME
DECLARE @Cmd nvarchar(200)
DECLARE @SpId int
DECLARE @logn nvarchar(30)
DECLARE @pgm nvarchar(max)
SET @Now = GetDate()
CREATE TABLE #Who2(
[SPID] int,
[Status] SysName NULL,
[Login] SysName NULL,
[HostName] SysName NULL,
[BlkBy] SysName NULL,
[DBName] SysName NULL,
[Command] SysName NULL,
[CPUTime] int NULL,
[DiskIO] int NULL,
[LastBatch] SysName NULL,
[ProgramName] SysName NULL,
[SPID2] int NULL,
[RequestId] int NULL)
INSERT #Who2 exec sp_Who2
DELETE FROM #Who2
WHERE Login IN ( 'sa' , 'visora' )
OR HostName='.'
OR ProgramName IN ('Java_Orbiter' , 'Tomcat7_Jsipert2')
OR DBName in ('msdb')
ALTER TABLE #Who2
ADD LastDate DateTime
IF Month(@Now)=1 And Day(@Now)=1
BEGIN
UPDATE #Who2
SET LastDate=
CASE WHEN LastBatch Like '12%'
THEN Cast( Substring(LastBatch,1,5)+ '/'+
Cast(Year(@now)-1 As varchar(4)) +' '+
Substring(LastBatch,7,8) as DateTime)
ELSE
Cast( Substring(LastBatch,1,5)+ '/'+
Cast(Year(@now) As varchar(4))+' ' +
Substring(LastBatch,7,8) as DateTime)
END
END
ELSE
BEGIN
UPDATE #Who2
SET LastDate=Cast( Substring(LastBatch,1,5)+ '/'+
Cast(Year(@now) As varchar(4))+' ' +
Substring(LastBatch,7,8) as DateTime)
END
DECLARE Hit_List CURSOR FOR
SELECT SPID, login, ProgramName FROM #Who2 Where Abs(DateDiff(mi,LastDate,@Now)) > 200
OPEN Hit_List
FETCH NEXT FROM Hit_List into @SpId, @logn, @pgm
WHILE @@FETCH_STATUS=0
BEGIN
SET @Cmd='KILL '+Cast(@SpId as nvarchar(11))+' /* '+@logn +' / ''' + @pgm + ''' */ '
EXEC(@Cmd)
PRINT @Cmd
FETCH NEXT FROM Hit_List into @SpId, @logn, @pgm
END
CLOSE Hit_List
DEALLOCATE Hit_List
DROP TABLE #Who2
GO
usingstatement. This confines the scope of the connection and ensures that it will be closed at the end of the block.