Killing idle users
-
Hi guys,
We have some third party software that sits on SQLServer 7 on NT 4.
Is there something out there that can be set up as a job in SQLServer to be run every half an hour looking for users that have been idle for 2 hours ?
Thanks in advance.
Dave
-
There might be a script in the library here, if not it's easy to write. Setup a stored proc to scan sysprocesses and check the last batch against the current time. After some xx time, run a kill against the spid.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com -
Just make sure it does not take a snapshot in time and comapre it to another snapshot in time otherwise you may take out users you don't mean to.
For example you do a select against sysprocess at 10:00 AM to get the spid and loginnae of each inactive user, do the same thing at 11:00 AN find user simley is still inactive.
Well smiley may have been active in the interval, or may have disconected and reconnected and gotten the same SPID.
If your aps are leaving hanging connections, deal with your aps.
Henry
-
OK, bringing som ehumour to the forum...
A couple days ago, in the QOD thread, a forum user remarked that really what the question was about was purely semantics. Brian Kelley responded that it was more than semantics:
quote:
To the DBA, that should all make sense. But even to a system administrator, some of that may be incomprehensible. Now, imagine you were Joe Blow on the streets. Talk about dropping tables probably has a totally different connotation than for the DBA.It just occurred to me that the title of this thread could probably be interpreted quite badly by our "Jow Blow on the streets".
Sorry for the tangent.
jay
-
As Henery pointed out, its better to handle the situation at app. level
but if you must you can use this proc to view and kill processes
I use the combined output of sp_who2 and DBCC INPUTBUFFER
Note: Define your criteria for idle process, and refine your search by updating
all occurrences of this statement:
Select *
From #Temp_Table
Where Login <> 'sa' AND
DatePart(hh, GetDate()) - Substring(LastBatch, CharIndex(' ', LastBatch) + 1, 2)) = @Time_Slept)
Ex. Where Login <> 'sa' AND HostName <> '...' so on ... AND
DatePart(hh, GetDate()) - Substring(LastBatch, CharIndex(' ', LastBatch) + 1, 2)) =
@Time_Slept)
** KILL statement has been commented out
CREATE Proc Show_Idle_Processes
@Show_Kill BIT = 0, -- 0 =(Just show idle pro.), 1 = (show and kill idle pro.)
@Time_Slept VARCHAR(4) -- In Hours (idle for # of hours)
As
Create Table #Temp_Table (SPID INT, Status VARCHAR(20), Login VARCHAR(40),
HostName VARCHAR(50), BlockedBy VARCHAR(10),
DBName VARCHAR(20), Command VARCHAR(255),
CPUTime INT, DiskIO INT, LastBatch VARCHAR(20),
ProgramName VARCHAR(100), SPID_2 INT)
Declare @spid INT, @DB_Name VARCHAR(20), @Last_Batch VARCHAR(4),
@Conn_Time VARCHAR(4), @Msg VARCHAR(100)
Exec('Insert #Temp_Table
Exec sp_who2 ')
Create Index Login On #Temp_Table (Login)
IF EXISTS(Select *
From #Temp_Table
Where Login <> 'sa' AND
DatePart(hh, GetDate()) - Substring(LastBatch, CharIndex(' ', LastBatch) + 1, 2) = @Time_Slept)
GoTo Show_Sleepers
Else
Begin
Select 'Found no connection(s) idle for more than '+@Time_Slept+' hour(s) !' As_Sleepers
Return
End
Show_Sleepers:
Create Table #Temp_Command (EventType VARCHAR(50),
Parameters INT,
Database_Name VARCHAR(20),
Last_CommandIssued VARCHAR(4000),
[Connection_Duration(HRS)] INT,
SPID INT NULL)
Select @spid = Min(spid)
From #Temp_Table
Where Login <> 'sa' AND
(DatePart(hh, GetDate()) - Substring(LastBatch, CharIndex(' ', LastBatch) + 1, 2)) = @Time_Slept
While (@spid IS NOT NULL)
Begin
Select @DB_Name = DBName,
@Last_Batch = Substring(LastBatch, CharIndex(' ', LastBatch) + 1, 2)
From #Temp_Table
Where spid = @spid
Set @Conn_Time = DatePart(hh, GetDate()) - @Last_Batch
Insert #Temp_Command (EventType, Parameters, Last_CommandIssued)
Exec('DBCC INPUTBUFFER ('+@spid+')')
IF(@@Error <> 0)
Begin
RAISERROR('Encountered error while preparing idle connections summary', 16, 1)
RETURN
End
Update #Temp_Command
Set spid = @spid,
Database_Name = @DB_Name,
[Connection_Duration(HRS)] = @Conn_Time
Where spid IS NULL
Select @spid = Min(spid)
From #Temp_Table
Where Login <> 'sa' AND
(DatePart(hh, GetDate()) - Substring(LastBatch, CharIndex(' ', LastBatch) + 1, 2))=
@Time_Slept AND (spid > @spid)
End
/** Create idel processes summary by combining sp_who2 and DBCC INPUTBUFFER results **/
Select T1.spid, T2.Login, T2.HostName,
T1.Database_Name, T1.[Connection_Duration(HRS)],
T1.Last_CommandIssued
Into #Temp_AllSleepers
From #Temp_Command T1, #Temp_Table T2
Where T1.spid = T2.spid
/** Show idel processes summary **/
Select *
From #Temp_AllSleepers
IF(@Show_Kill = 1)
GoTo Kill_IdelProcesses
Else
Return
Kill_IdelProcesses:
Select @spid = Min(spid)
From #Temp_AllSleepers
While (@spid IS NOT NULL)
Begin
--Exec('KILL '+@spid)
Set @Msg = 'Terminated idle process id '+Convert(varchar(8), @spid)
PRINT @Msg
IF(@@Error <> 0)
Begin
RAISERROR('Encountered error while terminating idel connection', 16, 1)
RETURN
End
Select @spid = Min(spid)
From #Temp_AllSleepers
Where spid > @spid
End
Edited by - mworku on 06/20/2003 6:59:30 PM
MW -
Many thanks.
I'll take what you've said on board, and see what I come up with.
Cheers
Dave
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply