KILL - 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)

KILL

Author

Topic

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-07-04 : 00:45:04

Dear All,
I need to kill all existing process in a database. Is Possible to kill all process in a single query.
Please advise me
-- KK

Kristen
Test

22859 Posts

Posted - 2006-07-04 : 00:51:03

Would this do?

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Kristen

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-04 : 00:56:43

I hope you're only doing this in a non-production environment.....

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-07-04 : 01:00:33

SSS that's not a production environment

-- KK

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-07-04 : 01:01:40

quote:
Originally posted by Kristen

Would this do?

ALTER DATABASE MyDatabaseName SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Kristen

I can't get clearly. What it will Do..?
Please explain me
-- KK

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-07-04 : 01:41:14

This is my Need
Create a SQL Script to identify all the existing processes and kill them

-- KK

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-04 : 02:38:37

select 'kill ' + convert(varchar(3),spid) from master..sysprocesses

run the generated script

--------------------
keeping it simple...

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-07-04 : 03:34:02

This only i need
Thanks Jen
-- KK

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-04 : 03:37:41

ok, my conscience is bothering me...

you do realize what the script will do? hope you filter out the server processes and just kill the user processes or you'll get into a bit of trouble... ok.. a lot then

--------------------
keeping it simple...

mr_mist
Grunnio

1870 Posts

Posted - 2006-07-04 : 04:07:16

quote:
Originally posted by jen

ok, my conscience is bothering me...

you do realize what the script will do? hope you filter out the server processes and just kill the user processes or you'll get into a bit of trouble... ok.. a lot then

Don't worry yourself Jen... SQL looks after itself -

Server: Msg 6107, Level 14, State 1, Line 1
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 2
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 3
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 4
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 5
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 6
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 7
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 8
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 9
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 10
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 11
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 12
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 13
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 14
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 15
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 16
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 17
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 18
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 19
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 20
Only user processes can be killed.
Server: Msg 6107, Level 14, State 1, Line 21
Only user processes can be killed.

-------
Moo. :)

CSK
Constraint Violating Yak Guru

489 Posts

Posted - 2006-07-04 : 04:43:30

select 'kill '+ convert(varchar(10), spid) from master..sysprocesses
-- to avoid the sql process
where status <> 'background'
-- to get the user process
and status in ('runnable','sleeping')
-- to avoid the current spid
and spid <> @@spid

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources