Kill all inactive sessions - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Kill all inactive sessions

Author

Topic

Elidas
Starting Member

33 Posts

Posted - 2008-05-21 : 08:50:01

Is there any way (maybe a sp_ procedure) to kill all the sessions that are doing nothing at the moment I launch the command?

As a maintenance task to clean everynight sessions that has not been correctly ended and are still loged on because the user forgot to close the session correctly or because there is a bugy application that leave the sessions open.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-21 : 09:32:28

Something similar to this?

DECLARE	@SPIDS TABLE (SPID INT)

INSERT	@SPIDS
	(
		SPID
	)
SELECT	SPID
FROM	master..sysprocesses
WHERE	NT_DOMAIN + '\' + NT_USERNAME NOT IN('\', 'NT AUTHORITY\SYSTEM')
	AND SPID <> @@SPID

DECLARE	@SPID INT

SELECT	@SPID = MIN(SPID)
FROM	@SPIDS

WHILE @SPID IS NOT NULL
	BEGIN
		KILL	@SPID

		SELECT	@SPID = MIN(SPID)
		FROM	@SPIDS
		WHERE	SPID > @SPID
	END

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources