Q

Kill specific bill pay sessions as scheduled task

I am a DBA supporting the development of a bill pay via telephone application. As it stands, after the call is done, the session remains. Is there a way to write a script to kill specific sessions as a scheduled task in our SQL 6.5 database?
You can certainly kill specific sessions, as defined by a spid, using regularly scheduled tasks. The problem is -- how do you know if you're killing a truly dead spid instead of one in which the user is simply not very active?

I'm sorry to say that I don't have a copy of SQL Server 6.5 still running. However, the following SQL Server 2000 script may be adaptable to your situation. It's called 'Kill all users in a DB' and I found it in the script library at SQLServerCentral.com.

 use master go declare @dbname sysname set @dbname = 'db2kill' -- substitute your database name here set nocount on declare Users cursor for select spid from master..sysprocesses where db_name(dbid) = @dbname declare @spid int, @str varchar(255) open users fetch next from users into @spid while @@fetch_status <> -1 begin if @@fetch_status = 0 begin set @str = 'kill ' + convert(varchar, @spid) exec (@str) end fetch next from users into @spid end deallocate users exec ('drop database ' + @dbname)

Now this specific script kills all spids in a given user database. You could just as easily set it to kill spids on any other aspect of sysprocesses.

 


Do you have comments on this Ask the Expert Q&A? Let us know.
This was first published in July 2005

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close