[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1252
  • Last Modified:

Killing orphaned connections.

My client has a situation such that they have small Java-based memory leaks that keep connections open (or orphaned) on our SQL Server.  The ultimate solution is to get a profiler and fix the problem.  But mod updates being what they are, this will not be completed until some time in June.  In the meantime, my client has written a script and asked me to give his blessing on whether it will kill the appropriate orphaned connections and leave the valid ones intact.  I am not particularly a DBA (a little knowledge..., a.k.a dangerous :) ).  The script is as follows:
 
use master;
go
declare @spid varchar(10);
declare @strSQL varchar(20);
declare c1 cursor read_only for
SELECT     dbo.sysprocesses.spid
FROM         dbo.sysprocesses INNER JOIN
                      dbo.sysxlogins ON dbo.sysprocesses.sid = dbo.sysxlogins.sid
WHERE     (DATEDIFF([day], dbo.sysprocesses.last_batch, GETDATE()) >= 2) AND (dbo.sysxlogins.name IN (N'webuser', N'aspaccess'))
ORDER BY dbo.sysprocesses.spid;
open c1;
fetch next from c1 into @spid;
while (@@fetch_status <> -1)
begin
     if (@@fetch_status <> -2)
         begin
           print ' KILLING: ';
           print ' SPID: ' +@spid;
           print '=========================';
           set @strSQL = 'KILL ' + @spid;
           EXEC (@strSQL);
         end
     fetch next from c1 into @spid;
end
close c1;
deallocate c1;
go


Can someone let me know if this will do what my client needs?

Thanks
0
James Cochrane
Asked:
James Cochrane
1 Solution
 
obahatCommented:
That would work, although you must tell your client that this is not a real solution to the problem, and he should fix/take care of the momery leaks. Also - it is not good practice to open cursors against system tables, you may wish to consider the following alternative solution:

DECLARE @SPID INT

IF OBJECT_ID('tempdb..#tbl', 'U') IS NOT NULL
      DROP TABLE #tbl

CREATE TABLE #tbl (SPID INT, Status VARCHAR(256), Login NVARCHAR(256), HostName NVARCHAR(256), BlkBy NVARCHAR(256), DBName NVARCHAR(256), Command varchar(2000), CPUTime BIGINT, DiskIO BIGINT, LastBatch VARCHAR(50), ProgramName NVARCHAR(256), SPID1 INT)

INSERT INTO #tbl
EXEC sp_who2

UPDATE #tbl
SET LastBatch = LEFT(LastBatch, 5) + '/' + CAST(datepart(year, getdate()) AS VARCHAR) + RIGHT(LastBatch, 9)

DECLARE c1 CURSOR READ_ONLY FOR
SELECT SPID FROM #tbl WHERE CAST(LastBatch AS DATETIME) <= getdate()-2

and the rest is as in your code

while (@@fetch_status <> -1)
begin
     if (@@fetch_status <> -2)
         begin
           print ' KILLING: '
           print ' SPID: ' +@spid
           print '========================='
           set @strSQL = 'KILL ' + @spid
           EXEC (@strSQL)
         end
     fetch next from c1 into @spid
end
close c1
deallocate c1
go


BTW - this need not run on the master. It's also not great to run cutom queries on the master, since it is not meant to be used by users.

Hope this helps.

---------------------------------------------
SQL Farms Solutions
www. sqlfarms.com
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now