Learn more about SQL Server tools


Latest SQL Server Tips

Free SQL Server Learning

SQL Server Audit with SQL Compliance Manager and SQL Secure

How to Roll your Own Value, RegEx and SoundEx Pattern Profiler in SSIS

Are You Making the Right Choices for SQL Server HA?

SQL Server Security Essentials

Implementing a SANLess SQL Server Cluster in Under an Hour

Obtain Exclusive Access to a SQL Server Database

MSSQLTips author Jeremy Kadlec By:   |   Read Comments   |   Related Tips: More > Scripts

In your earlier tip (Die Sucker - Killing SQL Server Process Ids (spids)) you outlined an option to kill spids (system process ids) to perform database restores or database maintenance when exclusive use of the database is needed.  Do any other options exist?  What are the advantages and disadvantages to these approaches?  When should one approach be used over another?

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.


  • SET - Determines the type of user access to the database
    • SINGLE_USER - Single user connection to the database
    • RESTRICTED_USER - Any number of users with db_owner or dbcreator or logins with sysadmin rights can connect to the database
    • MULTI_USER - Any number of users with rights to the database can connect to the database
  • WITH ROLLBACK - Determines how the exclusive access to the database will take place
    • ROLLBACK AFTER integer [SECONDS] - Rollback the spids after a particular number of seconds
    • ROLLBACK IMMEDIATE - Rollback the spids immediately
    • NO_WAIT - If all of the spids do not commit or rollback immediately the request to put the database in an exclusive state will fail


Option 1 (Restricted Access) - Restrict the database to users with db_owner, dbcreater or sysadmin rights and rollback the spids immediately
Option 2 (Exclusive Access) - Restrict the database to a single user and rollback the spids immediately
Option 3 (Multi User Access) - Unrestricted database access

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

Last Update: 5/21/2007

About the author
MSSQLTips author Jeremy Kadlec
Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as a SQL Server Consultant at Edgewood Solutions, co-founder of MSSQLTips.com as well as Baltimore SSUG co-leader.

View all my tips

print tip Print  
Become a paid author

join MSSQLTips for free SQL Server tips     

Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates

       Note: your email address is not published. Required fields are marked with an asterisk (*)

Get free SQL tips:

*Enter Code refresh code     

Sponsor Information