Killing orphaned connections.

Posted on 2005-04-15
Last Modified: 2008-02-01
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;
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 ( IN (N'webuser', N'aspaccess'))
ORDER BY dbo.sysprocesses.spid;
open c1;
fetch next from c1 into @spid;
while (@@fetch_status <> -1)
     if (@@fetch_status <> -2)
           print ' KILLING: ';
           print ' SPID: ' +@spid;
           print '=========================';
           set @strSQL = 'KILL ' + @spid;
           EXEC (@strSQL);
     fetch next from c1 into @spid;
close c1;
deallocate c1;

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

Question by:techhound
    1 Comment
    LVL 5

    Accepted Solution

    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:


    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)

    SELECT SPID FROM #tbl WHERE CAST(LastBatch AS DATETIME) <= getdate()-2

    and the rest is as in your code

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

    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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Via a live example, show how to setup several different housekeeping processes for a SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now