Deleting sleep -database processes

[Ernest74]

Hi all!

I have a problem with my SQL server. For various reasons my sql server is full of databse processes with status sleeping. By time this will eventually freeze the system.

I figured out the solution: I code this SQL Server job, witch will automatically delete all sleeping processes:

select 'kill '+convert(char,spid) from master..sysprocess
where
status = 'sleeping' and
spid > 50 and
AND DATEDIFF( minute, last_batch, GetDate() ) > 60

This select statement is working fine, i get result set of commands to kill the wanted prosedures. But thats all. My job does not actually run the command to kill the procedures.

I examined the situation also in the SQL Server query analyzer - I get result set of kill commands:

Kill 59
Kill 66
Kill 101

but thats all.

So now to my big question: How can I make it so, that the system will also run the kill commands. I have tried with ECEX etc, but I cant find the right way to do it. Could anyone please help me?

Ernest

 

[hmckillop]

Nightmare!!!
You are opening up a complete can of worms and may cause serious problems,
but if you are sure and you know youu want to deinfitely do this then

declare @ssql varchar(100)
SET @ssql = ''
WHILE @ssql IS NOT NULL
BEGIN
	select top 1 @ssql  = 'kill '+ convert(char,spid) from master..sysprocesses 
	where 
	status = 'sleeping' and 
	spid > 50 and 
	 DATEDIFF( minute, last_batch, GetDate() ) > 60 
	if @ssql = ''
		SET @ssql = NULL
	else
		exec (@ssql)
END

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Ernest74]

Thank you. You solved my big problem and I am very thankfull to you. I tried the new sql -command and it worked like charm.

I understand, that this is not the right way to do it. But now I am just happy, that the situation is somehow solved.

I also included one line to select:
program_name = '(program_name)' and

so I think the kill command should be no problem, because normally the programs database connections are ON maximum of 15 seconds.

Once again, thank you. Youve been great help!

Ernest

 

[mrdenny]

You will want to look into your application code. The underlying cause is that the app is opening connections and not closing them correctly.

Make sure that the app closes the connections, and destorys the connection objects.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)