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.
Home