|
|
|
|
|
|
|
|
Die Sucker - Killing SQL Server Process Ids (spids) |
|
|
|
|
Back
Home
Problem
I have noticed some of my processes are failing because spids are already connected
to the database. This happens specifically when I need to a restore database.
I catch this problem pretty quick when I am working on it during the data and can fix it,
but during nightly processing existing spids become problematic.
I have also noticed existing spids causing problems for my SQL Server 2000 Database Maintenance
Plans. I have found this issue in my logs specifically related to performing integrity checks
(DBCC CHECKDB ('YourDatabaseName') REPAIR_FAST) when the database needs to be in single user
mode before the integrity check commands run. How can I kill these spids prior to running my processes?
|
|
SolutionKilling the spids is the
process that needs to
occur prior to issuing
DBCC CHECKDB or
performing the database
restore process.
Killing the spids can be
accomplished by adding
another step to your SQL
Server Agent Jobs or in
your script\stored
procedure calling the
code below to perform
the KILL process prior
to executing code that
needs exclusive use of
the database.
|
SQL Server
2000 and SQL
Server 2005 |
USE Master
GO
SET NOCOUNT
ON
-- 1 -
Variable
Declaration
DECLARE @DBID
int
DECLARE
@CMD1
varchar(8000)
DECLARE @spidNumber
int
DECLARE @SpidListLoop
int
DECLARE @SpidListTable
table
(UIDSpidList
int IDENTITY
(1,1),
SpidNumber
int)
-- 2 -
Populate
@SpidListTable
with the
spid
information
INSERT INTO
@SpidListTable
(SpidNumber)
SELECT spid
FROM
Master.dbo.sysprocesses
WHERE DBID
NOT IN
(1,2,3,4) --
Master,
Tempdb,
Model, MSDB
AND spid >
50
ORDER BY
spid DESC
-- 3b -
Determine
the highest
UIDSpidList
to loop
through the
records
SELECT
@SpidListLoop
=
MAX(UIDSpidList)
FROM
@SpidListTable
-- 3c -
While
condition
for looping
through the
spid records
WHILE
@SpidListLoop
> 0
BEGIN
-- 3d -
Capture
spids
location
SELECT
@spidNumber
= spidnumber
FROM
@spidListTable
WHERE
UIDspidList
=
@SpidListLoop
-- 3e -
String
together the
KILL
statement
SELECT @CMD1
= 'KILL ' +
CAST(@spidNumber
AS
varchar(5))
-- 3f -
Execute the
final string
to KILL the
spids
-- SELECT
@CMD1
EXEC (@CMD1)
-- 3g -
Descend
through the
spid list
SELECT
@SpidListLoop
=
@SpidListLoop
- 1
END
SET NOCOUNT
OFF
GO |
Next Steps
-
Check your Database
Maintenance Plan
logs or SQL Server
Agent Job Step
History to determine
when existing spids
are preventing your
automated processes
from succeeding.
-
Depending on the
user and/or
automated processes
that are running
dictates how the
scripts above should
be modified to meet
your needs.
-
Although the scripts
above are beneficial
for you to execute
your process, you
may also need to
consider the other
processes that are
running and
determine if 1 or
more of the
processes need to be
executed at another
time period for all
of the processes to
complete in a
reasonable time
period. For
example, you may not
want to kill a
process at 4:00 AM
that needs 4 hours
to complete and must
be finished by 8:00
AM. In this
circumstance,
re-scheduling the
process may be a
better bet.
-
Stay tuned
for another tip on
situational
alternatives to
issuing the KILL
command.
Back
Home
|
|
|
|
|
|
|
|
Home