Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Elidas
Starting Member
33 Posts |
 Posted - 2008-05-27 : 03:43:55
|
| How can I configure SQL Server 2000 to close the connections that has been inactive/sleeping for more than "X" minutes? |
|
|
Elidas
Starting Member
33 Posts |
Posted - 2008-05-27 : 12:16:58
|
| anybody? |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-27 : 12:38:23
|
| You can use Kill command to end the connection but sleeping doesn't mean you can kill it as it might be waiting to start tran after 1 sec.If you want to disconnect after query executes:Then go to Tools-Options-SQL Server-Advanced |
|
|
|
monty
Posting Yak Master
130 Posts |
Posted - 2008-05-27 : 12:56:17
|
| DECLARE TestSPIDS CURSOR FOR SELECT spid FROM sysprocesses WHERE program_name LIKE 'xxx%'--change to intended one. AND last_batch < DATEADD(Hour, xx, GETDATE())--change xx to intended value OPEN TestSPIDS DECLARE @SPID AS INT FETCH NEXT FROM TestSPIDS INTO @SPID WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @STRSQL AS VARCHAR(255) SET @STRSQL = 'KILL ' + CONVERT(VARCHAR, @SPID, 4) -- PRINT @STRSQL EXEC(@STRSQL) FETCH NEXT FROM TestSPIDS INTO @SPID END CLOSE TestSPIDSDEALLOCATE TestSPIDScreate about script as a job step and shedule it according to your requirementits me monty |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-27 : 12:58:20
|
| Why does it matter that a connection has been "idle" for X number of minutes? Are you seeing thousands of these?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
Elidas
Starting Member
33 Posts |
Posted - 2008-05-28 : 04:55:21
|
quote:
Originally posted by tkizer
Why does it matter that a connection has been "idle" for X number of minutes? Are you seeing thousands of these?
Actually, yes. Somehow everyday around 50-100 connections that should have been closed are still open, even when the user PC has shut down SQL Server maintan forever that connection loged until someone kill it manually |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-05-28 : 09:56:43
|
| It must be kicking some jobs .Well, you can DBCC inputbuffer and see exactly they are running or see DBCC opentran for open transactions.The question is IS it causing any issues? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-05-28 : 12:38:30
|
quote:
Originally posted by Elidas
quote:
Originally posted by tkizer
Why does it matter that a connection has been "idle" for X number of minutes? Are you seeing thousands of these?
Actually, yes. Somehow everyday around 50-100 connections that should have been closed are still open, even when the user PC has shut down SQL Server maintan forever that connection loged until someone kill it manually
Why don't you fix the application code that is leaking these connections then? Killing "idle" spids should be a short-term solution only.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Database maintenance routines:http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx |
|
|
|
|
|
|
how to configure timeout/expire session?