Dynamic Kill SQL - 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 Administration (2000)

Dynamic Kill SQL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-10-21 : 08:55:49

Chandru writes "Hi, We have SQL Server 2000 database. There are batch jobs after 7pm on daily basis. We want to ensure that there is no active session open before starting the batch jobs. So we have to create dynamic SQL : Kill SID.
We can check the open session using dbcc opentran. The question is how to link the SID with 'Kill'.
We want to submit this also as a batch job just before the main jobs start.
Thanks
Chadnru"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-21 : 10:41:46

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14239

Jay White
{0}

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-10-21 : 15:02:31

Since you are on SQL2000 you also have the option of using ALTER DATABASE. Assuming the users are not members of the db_owner fixed database role then you could issue

alter database <databasename>
set restricted_user with rollback immediate

then run your job(s) and then set it back to multi user

alter database <databasename>
set multi_user

If your job(s) run serially then you could actually set it to single_user (from within the same job
- otherwise you might lock yourself out

)

HTH
Jasper Smith

ebersin
Starting Member

3 Posts

Posted - 2003-01-24 : 10:56:13

I implemented this code on our production server and it killed the connection for a process that was clearly exempt - it has the System Administrator role in the database, yet the connection was unceremoniously severed! The process is running from the SQL Agent, starting at 10:00 PM and running until it finds what it's looking for around 2AM. At ll:30 PM I run the script to release any nonqualified users from the database, but it stopped this process, too. Am I missing something?
Many thanks,
Emily

Emily

JamesH
Posting Yak Master

149 Posts

Posted - 2003-01-27 : 10:39:20

No critiqueing. It works just fine... This creates and executes the kill statements for a particular dbid. You

CREATE Proc sp_KillLazyConns
as

Set NOCOUNT ON
DECLARE @spid integer, @str varchar(255)

DECLARE KillUsers_Cursor CURSOR FOR
Select spid
From sysprocesses
Where dbid = 7
And loginname<> 'SQLServiceAccount'

OPEN KillUsers_Cursor
FETCH NEXT FROM KillUsers_Cursor
INTO @spid

WHILE @@FETCH_STATUS = 0
BEGIN
set @str = 'kill ' + convert(varchar, @spid)
exec (@str)

FETCH NEXT FROM KillUsers_Cursor
INTO @spid
END
CLOSE KillUsers_Cursor
DEALLOCATE KillUsers_Cursor
GO

Yeah, Yeah, Cursors are dumb...This isn't 6.5 anymore...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources