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)
 More Robust Kill Processes

Author  Topic 

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-16 : 06:34:17
Hi all,

I've reviewed a few stored procedure's around killing users - they all see somewhat similar in terms of the ACTUAL kill process - i.e. a cursor through spid's, running a KILL. that is pretty much what the code I inherited here does, as well.

http://www.sqlservercentral.com/scripts/contributions/30.asp
http://www.sqlservercentral.com/scripts/contributions/838.asp
http://www.sqlservercentral.com/scripts/contributions/1097.asp


However, this is not particularly robust, imho, since (as happened this morning) if a SPID is release between the running of the SP_WHO and the actual kill command, then the command fails:

*****
Attempting to notify users via net send. [SQLSTATE 01000]
spid ecid status loginame hostname blk dbname cmd
------ ----------- -------------------------------- -------------------------------- -------------------------------- -------- -------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
51 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
52 0 runnable CORP\2020ADM 0 pdblue SELECT
54 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
56 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
65 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
70 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
72 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND
Msg 6106, Sev 16: Process ID 51 is not an active process ID. [SQLSTATE 42000]
Msg 0, Sev 16: KILL 51 [SQLSTATE 01000]

*****

I'm looking to make it more robust, e.g.:

exec (@KillSQL)
select @ReturnCode = @@Error
if @ReturnCode <> 0
Print 'A problem : '+cast(@ReturnCode as char)
else
Print 'Success : '+cast(@ReturnCode as char)

which give's me:

kill 55
Server: Msg 6106, Level 16, State 1, Line 1
Process ID 55 is not an active process ID.
A problem : 6106

But I need to make sure that my process continue's since the failure to disconnect a spid that was gone caused my process to do log shipping to fail, and clearly that is by no means a critical failure - I need it to continue onwards...

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 07:54:07
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36633&SearchTerms=kill,user

Is that what you're looking for?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-16 : 08:31:31
@Derrick,

Not what I was looking for, but exactly what I need (I think )

I'll need to test it and see that it works exactly as I need it, but it seems perfect. the current process was:

Call sp_kill_users
list all users connected to database into #temp,
cursor through #temp users
net send all users connected to database,
kill all users
go back to calling sp
Set database single_user
restore with norecovery
Set database multi_user

Now it appears I can change that to:


net send all users connected to database
Set database single_user with rollback immediate
restore with norecovery
Set database multi_user


Thoughts?


*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 08:36:05
Looks good. You might want to actually maintain an email list of your users though.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-16 : 08:45:30
I am considereing it, but that will take a bit of time to get the list, since we are in a "tumulteous" time at work, so that list of users (this is a reporting environment we are creating via Log Shipping) would be constantly changing, and I don't know of a guaranteed way of ensuring my mail list is up-to-date as staffing changes. I might be able to find a consistent way of deriving email address from usernames...

The previous code built that net send and did the kill inside another SP, using a #Temp table which meant that the calling SP that does the log shipping couldn't send to let people know.

I actually think I'll be able to make quite a few enhancements now, cutting out at least 1 cursor, with this change

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2005-05-16 : 08:49:21
Well, anytime you can whack a cursor, that's good. Since you're using this for reporting, have you considered replication?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-16 : 08:58:21
Replication was consider as part of the 7 techonology options listed, but it is not an option due to the fact that several of the databases being Log-Shipped are external vendor's databases where we cannot alter database structures.

We are using the environment for Reporting and also certain point-in-time extracts (naturally business critical ), and some rudimentaty ad hoc BI queries (that's what comes from a limited budget), so we have some fun where, for example, external scheduling agents need to trigger the Log shipping (I'm using sp_start_job to do that from the external scheduling agent, as well as sp_update_job to enable and disable to ensure that a regularly scheduled log shipping doesn't run until the certain critical extracts are done).


*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-05-16 : 12:41:58
What's wrong with

ALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Brett

8-)
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2005-05-17 : 03:19:51
Hi Brett,

Nothing wrong with it - that is what Derrick pointed me too:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36633&SearchTerms=kill,user

And what I said I was moving to:
quote:

net send all users connected to database
Set database single_user with rollback immediate
restore with norecovery
Set database multi_user



Thanks

*##* *##* *##* *##*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page
   

- Advertisement -