Problem

Do any other options exist? What are the advantages and disadvantages to these approaches? When should one approach be used over another?

Solution

The typical approach to gaining exclusive use of a database is to use the ALTER DATABASE command. This command has a few different parameters that can be helpful without writing any custom code to gain exclusive or restricted access to a database. Check out the options and examples below in addition to some high level option analysis.

Option 1 (Restricted Access) - Restrict the database to users with db_owner, dbcreater or sysadmin rights and rollback the spids immediately ALTER DATABASE DatabaseName
SET RESTRICTED_USER
WITH ROLLBACK IMMEDIATE
  Option 2 (Exclusive Access) - Restrict the database to a single user and rollback the spids immediately ALTER DATABASE DatabaseName
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE
  Option 3 (Multi User Access) - Unrestricted database access ALTER DATABASE DatabaseName
SET MULTI_USER
 

ALTER DATABASE - Advantages and Disadvantages

• Advantages
  • Easily incorporated into scripts for database restoration processes
  • A fair amount of flexibility based on the options listed above without having to write any custom code
• Disadvantages
  • Could potentially have a long rollback process
    • The typical resolution would be to use the NO_WAIT option
  • Could potentially kill spids that need to finish for business reasons that are unexpectedly running slow

Kill spid Script - Advantages and Disadvantages

• Advantages
  • Can modify the script to determine if a specific application is running and determine if any spids should be killed or just wait for a finite period of time with a WAITFOR command
  • Can modify the script to conditionally issue the ALTER DATABASE statement as opposed to the KILL commands to determine if the database should be put in an exclusive state
  • Can modify the script to meet conditional needs
• Disadvantages
  • Could potentially have a long rollback process
    • Since you are using custom code and have knowledge of your environment you could conditionally kill spids as opposed to just killing all spids
  • Could potentially kill your own spid
    • The typical resolution would be to connect to a database that will not have spids killed as a portion of the process

Next Steps

• As is the case in many scenarios, a variety of options exist to address an issue or resolve a problem.  What is really necessary is to review the scenario, gather as much information as possible, determine the viable options then determine the best approach based on previous steps.  When it comes to killing spids for whatever reason, this scenario is no different.