Looking for SQL 2005 KILL SPID script

Question

Looking for SQL 2005 KILL SPID script

Express12

SSCoach

Points: 18514
More actions
May 11, 2007 at 8:56 am

#178506

Has anyone come across a script to loop thru and KILL all SPIDs on a given DB in SQL 2005?
My SQL 2000 KILL script is invalid because it references sysprocesses -- rendering this error msg:
Msg 208, Level 16, State 1, Procedure spr_Kill_Process, Line 33
Invalid object name 'sysprocesses'.
BT

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Express12 SSCoach Points: 18514 More actions · Answer 1

Actually, my existing 2000 script still works IF I create it in a non-system DB and exec it from there (and qualify the sysprocesses & sysdatabases tables w/ master..

CREATE procedure [dbo].[spr_Kill_Process] @dbname varchar(128) as

set nocount on

set quoted_identifier off

declare @kill_id int,

@query varchar(320)

declare killprocess_cursor cursor for

select a.spid from master..sysprocesses a

join master..sysdatabases b on a.dbid=b.dbid where b.name=@dbname

open killprocess_cursor

fetch next from killprocess_cursor into @kill_id

while(@@fetch_status =0)

begin

set @query = 'kill '+ convert(varchar,@kill_id)

exec (@query)

fetch next from killprocess_cursor into @kill_id

end

close killprocess_cursor

deallocate killprocess_cursor

BT

bledu SSChampion Points: 12335 More actions · Answer 2

so.. do you still need any help. i was about to look into this

Everything you can imagine is real.

Express12 SSCoach Points: 18514 More actions · Answer 3

nope.. all set.. But thx anyway.. (my alter ego kicked in)

BT

Greg Charles SSC-Forever Points: 45403 More actions · Answer 4

You can create the stored procedure in master if you qualify sysprocesses and sysdatabases with the schema sys. They are system views in SQL 2005 rather than system tables i.e. sys.sysprocesses & sys.sysdatabases.

Greg

Express12 SSCoach Points: 18514 More actions · Answer 5

Greg - thx for this info. Couple of questions:

1) How do I go about configuring the master DB for allowing updates? (didn't know you could update system tables in SQL 2005)

2) Can you supply a sample of qualifying sysprocesses, sysdatabases with the schema sys?

BT

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

1. In general, you can't update system tables. According to BOL, they may be accessed under certain circumstances using a "Dedicated Administrator Connection", but it's not recommended. See "Using a Dedicated Administrator Connection" in BOL.

2. select name, create_date from sys.sysdatabases

Greg