June 4, 2004 at 12:11 pm
I support alot of SQL 2000 installs mostly using Solomon and or custom software. I often notice that during the day that connections build up even after the users log out. I know its bad caused by bad programming already. I cannot change that in alot of this software, someone elses code. Now eventually they time out and go away. But it can be that 30 or more transactions from the morning stay active till the evening. Sometimes alot more.
All these applications are using ADO based data access (most in 2.7). Is there anything I can do in SQL to kill these faster, automatically, without killing ligitimate users from the software?
June 4, 2004 at 3:33 pm
I suppose you could set an "idle time" limit, and kill the spid when that limit is reached. You could query sysprocesses to determine idle time and the spid. It would also be possible to netsend the user before killing their spid, say give them 10 minutes, and if there has been no activity after that, then kill them. This could all be done within a stored procedure which you could schedule to run on a regular basis, say every 10 minutes.
Steve
June 4, 2004 at 3:40 pm
Thats a really good idea. I like the netsend thing. What would be the best way to get idle time? Its not in sp_who or sp_who2. Do I need to go to sysprocess to get it or is there a more elegant way?
June 4, 2004 at 3:46 pm
I'd just get it from sysprocesses. sp_who2 would work as well. Use LastBatch or keep track of CPUTime.
Steve
June 4, 2004 at 3:48 pm
Thanks, I'm gonna try it.
June 5, 2004 at 9:19 am
Just keep in mind if you step on a SPID that is making changes then all the work it is doing is going to attempt to roll back. Also be aware of the system SPIDs and don't step on those by accident or you can lock your server up. Also, unless you on a per client license model and are seeing performance issues be aware an active connection uses between 30k and 50k of memory only which performance wise may be better to leave alone. If you can leave alone I would but I would deffinently discuss with the person in charge of the programmers and state there seems to be an issue in their apps that needs immediat attention on their part and try and force them to fix the issue as it will only get worse over time and they will always make the same mistakes. Remember it isn't your job to fix their mistakes but you should prohibit new apps access until they learn to do it right.
June 6, 2004 at 11:50 pm
You said you won't go the direction of fixing the programs causing the problem?
I also suggest you request your programming department to look into it for two very important reasons:
1. The problem itself, could be related to not closing the ADO or other objects, which may take up memory until the system is restarted.
2. And if this is the case, this will build up over time and bring your system to its knees, and even crash it. By the restarting of your system or IIS everynight you will mask this potentially danerous problem.
Coach James
June 7, 2004 at 2:44 am
Try this. I would like to take credit for this but I can't. Its a handy little bit of script that I lifted from a forum some time ago. Hope it's what your looking for.
CREATE procedure usp_KillOldConnections
--@login varchar( 40) = NULL,
@latency int = 1440,
@debug tinyint = 1,
@db varchar( 40) = Datawarehouse
as
/*
*************************************************************
Name: usp_KillOldConnections
Description:
Utility stored procedure to remove any connections that have
been idle for a specified number of minutes. Only connections
logged in using the specified login and in the specified database
are removed. Only connections that are anonymous with no programname
are reported.
The debug parameter is used to either display the list of SPIDs
that meet the criteria or to kill them.
Usage:exec usp_KillOldConnections , , ,
Author: dkRanch.net (Steve Jones)
Input Params:
-------------
loginlogin name to remove connections for
dbdatabase name in which connection is in use
latency number of minutes of idleness for the connections
debug debug parameter. If 0, connections are killed. If 1,
the connections are displayed. defaults to 1
Output Params:
--------------
Return: error code. 0 = no error
Results:
---------
Calls:KILL
Locals:
--------
@errHolds error value
Modifications:
--------------
*************************************************************
*/
set nocount on
declare @err int,
@spid int,
@cmd varchar( 500)
select @err = 0
/*
Check parameters and exit if not correct.
*/
--if @login is null
-- select @err = -1
if @db is null
select @err = -1
if @err = -1
begin
Raiserror( 'Parameter Error:Usage:exec usp_KillOldConnections , , , ', 12, 1)
return @err
end
create table #Test
(spid int,
status varchar( 20),
login varchar( 40),
hostname varchar( 40),
blkby char( 5),
dbname varchar( 40),
command varchar( 500),
cputime int,
diskio int,
lastbatch varchar( 20),
programname varchar( 80),
spid2 int
)
insert #test
exec sp_who2
declare OldUsers cursor for
select spid from #test
where dbname = @db
-- and login = @login
and cast( substring( lastbatch, 1, 5) + '/01 ' +
substring( lastbatch, 7, 8) as datetime) < dateadd( mi, 0-@latency, getdate())
-- and programname = ' '
open OldUsers
fetch next from OldUsers into @spid
while @@fetch_status = 0
begin
select @cmd = 'kill ' + cast( @spid as char( 10))
if @debug = 1
print @cmd
else
exec( @cmd)
fetch next from OldUsers into @spid
end
deallocate OldUsers
drop table #test
return @err
GO
June 7, 2004 at 3:13 am
HI,
I totally agree with Antares and Coach James - it is the programmers department to sort this out. I have a very similar situation with Sybase EAServer application where connections are building up, taking with them server resources. Not a good idea as this will generally lead to restarting the sqlserver. This has happened on a daily basis.
However, I have an "easier" task as there is only one person working on this application and I can track this person down quite easily. Gues what? I know that the SQLServer will perform as it should either when this person in question is on holiday or sick...like today!
Nonetheless, it is a pain Edward.
June 7, 2004 at 5:15 am
I also have to concur that the programmers should address this issue, not the DBA.
As a programmer, I know that it is important to create applications that manage resources efficiently. If I do not address this issue, then the program will cause the problems you are encountering.
However, I must stress that you work with the programmers and not against them. Many programmers that I have encountered did not realize the connection issues involved with a SQL Server database. After demonstrating the problem, usually they were more than happy to make the changes necessary. If they were not then I had to use the "chain of command" to make the changes necessary.
Of course, if you don't own the application(3rd party vendor or contractor) then I suggest working with them to resolve the bug. Again, if they are professionals, they will be happy to fix the problem if they see the proof that it is the application that is causing the problem.
Good Luck,
Al
"I will not be taken alive!" - S. Hussein
June 7, 2004 at 5:51 am
I agree and I believe I was Little strong in my statement as was a bad day with another developer when I wrote. 99.9% are always happy to fix and learn from their mistake. But if there is an issue and they are not addressing kindly but assuredly don't allow more of the same mistake to add load to your box until they address (that is your job as a DBA).
June 7, 2004 at 6:00 am
Hehe... I have had my bad days with inexperienced programmers as well.
I am in a career position where I am both DBA and programmer for my organization. Most of the resource problems that I have encountered were from contractors mistakes. As I said, once the evidence was presented to them, they were happy to make the changes and learn from their mistakes. Many times I even helped them find the coding solution to fix the problem. Call me sick, but I just like to get into the nuts and bolts of applications at all levels. 🙂
- Al
"I will not be taken alive!" - S. Hussein
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply