This is one of those database topics that sounds really easy on its face but can be tricky in practice. The issue is how to gain exclusive access to a database so you can perform operations like taking it offline, etc. Unfortunately it’s not as easy as the documentation (BOL-Books Online) tries to make it sound and depending on what you’re trying to do it can actually be quite difficult.
Like we said, you may choose to do this is different ways depending on your ultimate goal and the type of system you’re on. First let’s talk about the way that is strictest (and the most protective) of your data. This method is what you’ll use if you’re working on an OLTP system where you don’t want to lose any transactions.
ALTER database Mydatabase
SET single_user
WITH rollback immediate
To explain the command a bit we’ll discuss each element.
ALTER database Mydatabase – This is simply the command you’re trying to run. You want to alter this database.
SET single_user – This is the parameter that you use to tell the database to only accept one user and no more. The problem, as you’ll see later, is that it can be any user that gets there first. Other choices for this command are restricted_user and multi_user. Restricted_user will restrict database access to the db_owner group only (and the specified DBO for the database itself), while multi_user will allow all connections.
WITH rollback immediate – This is where the real magic happens. This parameter tells SQL Server to disconnect all users and rollback their transactions immediately. Unfortunately, as you’ll see in the next section, this can be deceiving.
Ok, so you’ve typed the code above and all of the current transactions are rolling back. That doesn’t mean that you’ll have the database to yourself any time soon though. See, depending on how big the transactions are and how far along they are, it may be rolling back for several hours. Take for example if someone is running a large UPDATE or DELETE operation and it’s been running for 6hrs. When you run the above command it’ll start rolling it back immediately, but it won’t be finished for at least 6hrs. This is why we say that this parameter can be misleading, because for those who don’t know enough about it, it seems like your exclusive access is going to happen right away. This is one of the reasons DBAs are always preaching about small transactions. If you have to gain exclusive access to a database for any reason, you’ll be far more likely to do it if your transactions are small and there’s not much to rollback.
The other options for this command are AFTER and NO_WAIT. AFTER is a numeric value given in seconds. So if you want SQL Server to wait for 5 seconds before attempting to rollback any transactions, you would type ROLLBACK AFTER 5. This can be handy if you know a process is almost done and you want to give it time to complete instead of forcing it to rollback. It’s doubtful that you would give something only 5 seconds to complete so you would typically either wait until you can see that it’s done, or give it a couple minutes or so. NO_WAIT is another option that’s deceiving. It doesn’t mean the same thing that NOWAIT means in the section below. Here it means that if the transactions can’t be killed immediately, that is, if they will have to rollback, then cancel the ALTER DATASBASE request. This is handy if you want to know whether you’ll be able to get exclusive access right away, and if you’re not then you may choose another method.
Now, if you’re on a reporting system where there won’t be any real business transactions, then you can still use the rollback method above, but you may also want to consider using SHUTDOWN. All you have to do is open a query window in SSMS and type: SHUTDOWN with NOWAIT. That tells SQL Server to turn off the service immediately. What this really does is cause SQL Server to not perform a checkpoint on all the databases before they’re shutdown (we explain checkpoint here). If you do this on the wrong type of system you will force SQL Server to do a recovery process when you turn the service back on. Depending on what was happening when it was shut down, it could be hours before your database is available. So be careful with this command and know what type of system you’re working with.
Unfortunately, it’s not that easy because while you were able to shutdown SQL Server, which definitely kicked all the users out of the database, you still have the problem of gaining exclusive access. Often times you have processes running against your database and once SQL Server comes back up, they immediately begin trying to connect again. So this leaves you with a freshly restarted SQL Server, but with plenty of user connections attached. However, at this point it shouldn’t be too difficult to use the rollback command above because nothing has really had time to get lots of work done, so the rollbacks should be fairly quick.
All of this discussion is mostly for times when you have no choice but to do these operations right away. Let there be no mistake though: there’s simply no substitute for planning this type of operation and bringing your database into single_user mode in a graceful and stable fashion. The best way to accomplish this is to turn off all of your clients or shut down your website, or whatever is connecting to your database. A planned outage is the best way to ensure that nothing will go wrong, and it will make the whole process much faster. This way you know you won’t have anyone else trying to get into the database while you’re working, and you know you won’t have any job kicking off and trying to do something it shouldn’t. And for this reason it’s always best to also turn off the Agent so that you don’t do any damage or take up any resources that your process needs.