January 23, 2007 at 12:42 pm
Hi everyone,
When I run sp_who2, I see a whole lot of open connections that are several days old. For example, I see a couple of dozen backup connections from Veritas that are several days old. Why doesn't SQL 2K drop these connections? How can I change this behavior? Thanks in advance for your help.
January 23, 2007 at 2:43 pm
SQL server will not drop/clean old/orphaned connections..
If any application is connecting, application should close its connection...SQL doesn't know what the application's request/requirements until it gets it...
YOu can write a script to clean/kill old connection by querying sysprocess table...
MohammedU
Microsoft SQL Server MVP
January 23, 2007 at 2:46 pm
First off I would talk to whomever is on charge of your backups and Veritas. Veritas should not leave dangling connections (if they really are). In order to determine if they are dangling connections left over, you should query the sysprocesses table and compare the columns of lohin_time and last)batch to determine if the connection is actualyy doing or has done anything recently.
Another possibility is that you are using some kind of backup agent within Veritas. This would explain lingering connections. But to truly make the determination you will need to talk to your backup admin and maybe even Veritas.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
January 24, 2007 at 1:38 pm
If you are trying to get rid of spids i have a few queries that you can make into jobs that will do the trick for
you. Good luck
SET ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
procedure [dbo].[killuserqueries_idle] as
begin
declare @spidnum int
declare @cmd varchar(8000)
DECLARE userprocs CURSOR FOR
SELECT distinct spid from master..sysprocesses where spid > 50 and cmd = 'AWAITING COMMAND' and program_name like ('%query%')
OPEN userprocs
FETCH NEXT FROM userprocs into @spidnum
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = 'kill '+cast(@spidnum as varchar(10))
exec (@cmd)
FETCH NEXT FROM userprocs into @spidnum
END
CLOSE userprocs
DEALLOCATE userprocs
End
--PART 2
SET
ANSI_NULLS ON
GO
SET
QUOTED_IDENTIFIER ON
GO
CREATE
procedure [dbo].[killuserqueries] as
begin
declare @spidnum int
declare @cmd varchar(8000)
DECLARE userprocs CURSOR FOR
SELECT distinct spid from master..sysprocesses where spid > 50
OPEN userprocs
FETCH NEXT FROM userprocs into @spidnum
WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = 'kill '+cast(@spidnum as varchar(10))
exec (@cmd)
FETCH NEXT FROM userprocs into @spidnum
END
CLOSE userprocs
DEALLOCATE userprocs
end
January 24, 2007 at 4:52 pm
I was trying to do some work and found the same issue. Then after testing, I found that if you CANCEL a running job the connection is not dropped. So those old connections might be cancelled jobs. I have found that they do 'clear out' after a few days.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply