Share via

Forcing Termination of User SQL Sessions so I can "DROP DATABASE"

  • Article

Once again from the internal dbtalk alias. The person in question recreated their database from scratch as part of  a batch job every night and had

 

USE MASTER

GO

DROP DATABASE FOO

GO

in their script and obviously it was not working. The basic problem here is another process in the db that prevents it from being dropped, so how do we address this?

There are several options the ones I have seen most often are to enumerate spids and then kill wound (those of you who have been using SQL Server for a number of years will know that kill used to be more like "intimidate slightly", in 2005 its more like a "vicious maming", one day we'll really do the deed) them. A more common approach is to alter the database's <db_user_access_option> setting. BOL is our friend here so I am just going to C&P the contents, with a highlight.

<db_user_access_option> ::=

Controls user access to the database.

  • SINGLE_USER
    Specifies that only one user at a time can access the database. If SINGLE_USER is specified and there are other users connected to the database the ALTER DATABASE statement will be blocked until all users disconnect from the specified database. To override this behavior, see the WITH <termination> clause.

    The database remains in SINGLE_USER mode even if the user that set the option logs off. At that point, a different user, but only one, can connect to the database.

    Before you set the database to SINGLE_USER, verify the AUTO_UPDATE_STATISTICS_ASYNC option is set to OFF. When set to ON, the background thread used to update statistics takes a connection against the database, and you will be unable to access the database in single-user mode. To view the status of this option, query the is_auto_update_stats_async_on column in the sys.databases catalog view. If the option is set to ON, perform the following tasks:

    1. Set AUTO_UPDATE_STATISTICS_ASYNC to OFF.
    2. Check for active asynchronous statistics jobs by querying the sys.dm_exec_background_job_queue dynamic management view.
    3. If there are active jobs, either allow the jobs to complete or manually terminate them by using KILL STATS JOB.
  • RESTRICTED_USER
    RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused.
  • MULTI_USER
    All users that have the appropriate permissions to connect to the database are allowed.

The status of this option can be determined by examining the user_access column in the sys.databases catalog view or the UserAccess property of the DATABASEPROPERTYEX function

 

The SINGLE_USER issue is something to be careful of, I have met several customers where this has been a very real problem that after setting it they then can't get into their own database, never mind drop it.

Ok so we are going to stop anything else from using the database but what about everything thats (potentially) in flight, well we need to roll it back, again BOL is our friend.

WITH <termination>::=

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. If the termination clause is omitted, the ALTER DATABASE statement waits indefinitely if there is any lock on the database. Only one termination clause can be specified, and it follows the SET clauses.

  • ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
    Specifies whether to roll back after the specified number of seconds or immediately.
  • NO_WAIT
    Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

So the answer that worked was in fact:

alter database FOO set restricted_user with rollback immediate

go

drop database FOO

go

But SINGLE_USER would likely also have worked and is a little bit cleaner.

 

Comments

  • Anonymous
    October 03, 2007
    PingBack from http://www.artofbam.com/wordpress/?p=5028

  • Anonymous
    October 03, 2007
    you're spoiling one of my DBA interview questions !! good point about the async stats though.