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.

Webmaster Said:
Thank you.