Running Amok! Amok! Amok!

Day 1 – My partners-in-crime, err… co-workers have left me in charge of our little data kingdom whilst they rub elbows with the SQL elite at the PASS conference  in Seattle. Luckily for everyone, they anticipated my needs by bequeathing to me a survival kit in their absence.  Surprisingly, I succumbed to the cookies must later than anticipated. It’s doubtful the chocolate will last the week. May have to resort to less-than-savory methods to acquire more chocolate, if necessary. Considering it’s a 3 day work-week, that may not be what is referred to as a “good sign.” The duct tape has proved invaluable already. One developer asked what was in the kit. When shown the duct tape, she left post-haste. Hmm… must use this to my advantage.

So far it has been unnaturally quiet. I managed to get some work done today with few interruptions which lead to actual *gasp* progress!  Note to self: create mini-replicas of database team so the servers do not notice they are absent. It is also essential to locate pixie dust in the event of a server revolt.

Per request from one of our devoted minions, I created a script *an evil script?* to kill multiple user spawned processes for a specific database. I shall document it here in my journal for posterity. Authors Note: There is no guarantee a user process won’t have a spid less than 50 or a system process won’t have a spid greater than 50, I’m striking my original comment about it and removing it from the code below.  Must ensure the process is greater than 50 to preserve system processes.    

--SQL Server 2000:
declare @processList varchar(200)
set @processList = ''
if exists(select 1
          from master.dbo.sysprocesses
          where db_name(dbid) = 'kingdomOfNee')
          -- and spid > 50)
begin
    select @processList = @processList + ' kill ' + cast(spid as varchar(4)) + ' '
    from master.dbo.sysprocesses
    where db_name(dbid) = 'kingdomOfNee'
    -- and spid > 50


    execute(@processList)
    select spid from master.dbo.sysprocesses where db_name(dbid) = 'kingdomOfNee' --and spid > 50
end

In addition, the code above will work great for SQL Server 2000. For SQL Server 2005 and later, there’s a DMV for that. It’s called sys.dm_exec_sessions.  Big thanks to Aaron Bertrand for reminding me of it’s existance 🙂  What’s great about it is that it has a column called “is_user_process” that tells you if that process is *you guessed it* a user process. If the value is a 1, it’s a user process. If it’s a 0, it’s a system process. Simple, right?  The only issue I have with using it is that it doesn’t have a column for the database id. The sys.dm_exec_requests DMV does, but this DMV returns information on active requests – not all connections. This particular server has more than one user database on it and I need to drop the connections to just the one database. Luckily, there is yet another option that Mr. Bertrand reminded me of. Yes, I’m having too many chocolate deprivation moments lately. 🙂 

Another option is to set the database to single user mode with immediate rollback. The immediate rollback part will rollback any transactions that are running at that time. This should drop all connections to the database. The actual command looks like this: 

ALTER DATABASE 'KingdomOfNee' SET SINGLE_USER WITH ROLLBACK IMMEDIATE 

To put it back to allow multiple connections, the command looks like this:

ALTER DATABASE 'KingdomOfNee' SET MULTI_USER

It should work. Will need to think about it more and run it by my team since the script will have to run via a job at 0’dark thirty in the morning during the weekend.

Must find time on days off to work on my first ever presentation for our user group meeting next week. My DBA buddy informed me she will actually attend if I speak on CTEs (Common Table Expressions). Hmm… mind control experiment recommencement may prove to be successful. My evil plan will soon be a reality. It is only a matter of time… muhahaha… Note to self: find mindless minions to do evil bidding.

Sort-of-Serious Side Note: Rumors of my demise have been greatly exaggerated. No, really. Well, provided anyone noticed. Kidding! 🙂 This last month has been fraught with kin folk and perilous quests (i.e. family time and work projects). Therefore, I haven’t had time to work on my blog. It is my desire to remedy that. Therefore, I plan to at least ease my way back to blogging through a little journal of what happens when left in charge of the databases for a week since the rest of my team is in Seattle attending the PASS conference. My intent is to finish my little series on my daily database server script very soon, hopefully this Friday or possibly the next. Worst case, I should just aim for “before the end of the year… this year.” 🙂

Advertisements

5 thoughts on “Running Amok! Amok! Amok!

  1. Just a couple of points:

    1. Why not use ALTER DATABASE …SET SINGLE_USER WITH ROLLBACK IMMEDIATE? This will have the same effect as your loop kill routine.

    2. The SPID = 50 boundary is not guaranteed. There are columns in the DMVs that will be better for differentiation (sys.dm_exec_sessions.is_user_process)

    1. Thank you for reading and commenting. 🙂 You are absolutely right on both points. Doh! I must be suffering from chocolate deprivation, yeah that’s it. 🙂 Seriously, I need to re-evaluate using the alter db command. It sure would be simpler. Also, I really should use the DMV if I go the route of the kill. I guess some habits are really hard to change, at least in my case. 🙂

    2. I finally remembered why I used the kill approach instead of the alter database. I need to kill just the user accounts but not the service account. They have a separate way of stopping the service account. So I need to adjust the script to ensure I kill only the user accounts and leave the service account alone. There is a method to my madness. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s