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.asphttp://www.sqlservercentral.com/scripts/contributions/838.asphttp://www.sqlservercentral.com/scripts/contributions/1097.aspHowever, 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 COMMAND52 0 runnable CORP\2020ADM 0 pdblue SELECT 54 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND56 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND65 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND70 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMAND72 0 sleeping CORP\2020ADM 0 pdblue AWAITING COMMANDMsg 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 = @@Errorif @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 1Process 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,userIs that what you're looking for?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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_userslist all users connected to database into #temp,cursor through #temp users net send all users connected to database, kill all usersgo back to calling spSet database single_userrestore with norecoverySet database multi_user Now it appears I can change that to:net send all users connected to databaseSet database single_user with rollback immediaterestore with norecoverySet database multi_user Thoughts? *##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
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. MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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! |
|
|
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?MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
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! |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-05-16 : 12:41:58
|
What's wrong withALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATEBrett8-) |
|
|
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,userAnd what I said I was moving to:quote: net send all users connected to databaseSet database single_user with rollback immediaterestore with norecoverySet database multi_user
Thanks*##* *##* *##* *##* Chaos, Disorder and Panic ... my work is done here! |
|
|
|