Kicking users out of a database for…

All sorts of purposes: maintenance, rebuilding the database objects, dropping/recreating the database in test environments, changing a state option like readonly (did you know that no users can be using the database when you make this change to RO)….


Anyway, lots of reasons and this has always been the case. In previous releases we used to create sp_ (you know this is a special naming convention for objects in master that can be referenced in any database WITHOUT three-part naming) stored procedures that would detect the users using a database and either KILL spid directly or do a net send and then use WAITFOR DELAY to give the users a couple of seconds/minutes/whatever, to complete. Well, procedures like that are still useful (because you can set the database to “dbo use only,” warn the user, and then give them some time… However, warning them typically uses xp_cmdshell (which a lot of you don’t allow) and sometimes you don’t want to wait and/or give them much time :) :).


So – did you know that ALTER DATABASE in SQL Server 2000 has been changed to allow termination options for STATE changes. The termination options allow one of three types of termination:



  • Give the users n number of seconds to wrap things up

    • ALTER DATBASE dbname SET READ_ONLY WITH ROLLBACK AFTER

  • Kick them out immediately

    • ALTER DATBASE dbname SET READ_ONLY WITH ROLLBACK IMMEDIATE

  • Or, optionally you can have your process NOT WAIT

    • ALTER DATBASE dbname SET READ_ONLY WITH NO_WAIT

If no options are set and the process cannot get exclusive use of the database then it will till it can – indefinitely. Some database option changes generate a “could not get exclusive use” error but READ_ONLY (and a few others) don’t. If this is in an automated script/process this can cause you a lot of grief. (In fact, I’m adding this note about 90 minutes after I created this blog entry…because my QA window is still trying to get exclusive access to pubs – which I was setting to readonly just to see if it would eventually time out….well, 90 minutes and still running it hasn’t. I think I’m going to kill it!)


For a good example of how to change certain state changes when creating batch processes see, DB Alter for Batch Operation.sql (6.3 KB).

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.