Jun 8

Written by: Steve Gray
6/8/2010 4:29 PM 

It’s easy enough to Kill one SPID, in SSMS you open Activity Explorer, right click on a SPID and Kill it.

But, what if you’re like me, and you write code that doesn’t properly dispose of connections, and you generate 100 active connections and then your app crashes with the dreaded error “Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.”?

Most of you are saying ‘so don’t write code like that’… Easy enough for you to say.

Today I’m writing boilerplate eConnect code (integrations into the Microsoft Dynamics GP accounting system) and the eConnect code is hanging the connections. Now, I’m humble enough to know that the error could be on my part… and I’m trying to get that figured out. In the meantime, Activity Explorer has 100 open connections in my name.

Part one – code to VIEW the connections.

select    p.spid,
        convert(char(16), p.cmd) as cmd, 
        convert(char(10), p.status) as status,
        p.blocked,
        p.waittype,
        p.waittime,
        convert(char(20), p.lastwaittype) as lastwaittype,
        convert(char(25), p.waitresource) as waitresource,
        convert(char(30), d.name) as dbname, 
        convert(char(25), p.loginame) as loginname, 
        convert(char(15), p.hostname) as hostname, 
        p.cpu,
        p.physical_io,
        p.memusage, 
        convert(char(19), p.login_time, 20) as login_time,
        convert(char(19), p.last_batch, 20) as last_batch,
        p.open_tran,
        convert(char(12), p.net_address) as net_address,
        convert(char(12), p.net_library) as net_library
    from  master.dbo.sysprocesses p (nolock), master.dbo.sysdatabases d (nolock)
    where p.dbid = d.dbid
        and p.loginame like 'V-GP-02\eConnectSvc%'

You’ll want to change that last line to the correct user name.

Part Two: code to Kill the SPIDs:

 

DECLARE @spid int
declare @sql varchar(100)
DECLARE curName CURSOR LOCAL FAST_FORWARD FOR 
    select    p.spid
        from  master.dbo.sysprocesses p (nolock), master.dbo.sysdatabases d (nolock)
        where p.dbid = d.dbid
            and p.loginame like 'V-GP-02\eConnectSvc%'
OPEN curName
WHILE 1=1 
BEGIN
    FETCH NEXT FROM curName INTO @spid
    if @@fetch_status <> 0 begin
        break
    end 
    
    set @sql = 'kill ' + convert(varchar(10),@spid)
    exec (@sql)
END
CLOSE curName
DEALLOCATE curName 

Again, you’ll want to change that reference to “p.loginame”

Use this at your own risk. If you key it wrong it might cause… issues.