3
\$\begingroup\$

I have an application that has hundreds of users. Every now and again we need all users out of the application to run apply a fix on the data in DB (edit tables, causing triggers to be disabled during fix, etc). We don't want to set the DB to SINGLE_USER mode, which would kill the connections and ROLL BACK TRANSACTIONS, since often the work is being done by a third part remotely. So, we wrote the script below to remove Domain users and then set it to RESTRICTED_USER. I'm not overly concerned with performance since the query shouldn't take long, but am concerned with unanticipated results, underlying flaws that I'm not aware of, and what would be best practice in this case.

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


declare @domain varchar(64) = 'myDomain'

--used to exclude domain logins running on the server, like an agent account, etc...
declare @server varchar(64) = 'theServer'

--create temp to store sp_who2 results
if object_id('tempdb..#Who2') is not null drop table #Who2
create table #Who2 (
                    SPID int, 
                    Stat varchar(4000), 
                    LoginUser varchar(256),
                    HostName varchar(256), 
                    BlkBy varchar(256), 
                    DBName varchar(256), 
                    Command varchar(4000), 
                    CPUTime bigint, 
                    DiskIO bigint, 
                    LastBatch varchar(64), 
                    ProgramName varchar(256), 
                    SPID2 int, 
                    RequestID int
                    )

--load sp_who2 results
insert into #Who2 exec sp_who2

--delete all users who aren't domain accounts
--remove your SPID from the list to be deleted
--remove anything running locally on the box
delete from #Who2 
where 
    LoginUser not like + @domain + '%' 
    or HostName = @server 
    or SPID = @@SPID

--return the logins that will be dropped
select LoginUser as LoginsToBeDropped from #Who2


--cursor for the SPIDs
declare cur cursor fast_forward for 
    select
        SPID
    from #Who2

declare @Spid varchar(16)
declare @sql varchar(4000)

--loop through the SPIDs and kill them
open cur  
fetch next from cur into @Spid

while @@FETCH_STATUS = 0

    begin
        --select @Spid
        set @sql = 'kill ' + @Spid
        exec(@sql)
        fetch next from cur into @Spid  
    end 

close cur
deallocate cur
go

--see what connections remain
exec sp_who2
\$\endgroup\$
  • \$\begingroup\$ What would stop them from just immediately logging back in? \$\endgroup\$ – paparazzo Mar 13 '17 at 15:19
  • \$\begingroup\$ @Paparazzi we set the DB to RESTRICTED_USER after we kill connections \$\endgroup\$ – scsimon Mar 13 '17 at 15:24
  • \$\begingroup\$ You should put that IN the question. \$\endgroup\$ – paparazzo Mar 13 '17 at 15:28
  • \$\begingroup\$ Ok @Paparazzi, done \$\endgroup\$ – scsimon Mar 13 '17 at 15:29
  • 1
    \$\begingroup\$ Rather than delete from #Who2 you could just revise the select but that is pretty minor. \$\endgroup\$ – paparazzo Mar 13 '17 at 15:36
1
\$\begingroup\$

When you want users out of the database, do you have a timeframe as to when they should be allowed back in? The script you have above, will get rid of the users, but those users can just create a new connection to the database. What will you do when the user reconnects? Do you need to keep them out of the database for the entire time of the maintenance? What is the goal with keeping the users out of the database during the maintenance?

Additionally, if you kick people off out of the database, are you ok with losing the transaction that may be in flight?

What kind of maintenance do you want to do the database? Is it being done during a time of day when users should not be using the database?

|improve this answer|||||
\$\endgroup\$
  • \$\begingroup\$ The time frame varies for when they are allowed back. We place the DB is RESTRICTED_USER mode after connections are killed. Goal of keeping them out is for tasks which require changes in the data itself, thus some triggers need to be disabled, data manipulated, and then enabled. If users committed transactions while triggers were disabled--you can understand the issues. We are ok with killing the TRAN since they are warned prior to this step to get out of the system. We don't blind side them. maintenance was a bad word... it's almost always "fixes". Editing the question to fix that grammar. \$\endgroup\$ – scsimon Mar 13 '17 at 15:11
  • \$\begingroup\$ In this case, you are going to kill the connection. An active traction should roll back since it was not committed and if you are using transactions. Otherwise, setting to single user mode with rollback and killing current connections will net with the same result with one exception. The way you have set up the code will not stop new connections. \$\endgroup\$ – Chris Lumnah Mar 13 '17 at 15:16
  • \$\begingroup\$ The point of not using SINGLE_USER is to avoid it getting "stuck" in SINGLE_USER mode, especially when the "fixes" could be being applied from 3rd party vendors, support, etc. I can't argue that it's not the cleanest and easiest method and definitely was the first recommendation with rollback immediate. \$\endgroup\$ – scsimon Mar 13 '17 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.