|
|
|
|
|
|
|
|
Latest MS SQL Server Articles Research and Updates
|
|
Learn about Die Sucker - Killing SQL Server Process Ids (spids)
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?
Solution
Killing 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
scriptstored 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.

|
|
|
|
Comments
(On Die Sucker - Killing SQL Server Process Ids (spids))
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Interview Questions and Answers.
Which topic would you like to explore? Judge your ability in HTML, XML, C++, Networking, CSS, Database, JavaScript, PHP, SQL, VB and a lot of web and desktop programming techniques. GGL provide you to share interview questions, comment and any questions.
Start your choice of Interview Questions and Answers.
|
|
|
|
|
|
|
|
|
|
|
|
|
HTML Tutorial
will develop your web programming skill. HTML stands for Hyper Text Markup Language.
HTML file contains some predefined tags called markup tags like <html> is a tag..
Markup tags contains the data called content of the web page.
Content of the web page will be visible on web page by web browser.
HTML file must be save with the extension of .html or .htm
No software is required to create HTML files just use simple Note Pad.
Learn HTML with hundreds of examples.
|
|
|
|
|
|
|
|
|
|
|
|
|
JavaScript Language
is a simple programming language built into Netscape 2.0 and greater.
It is integrated with and embedded in HTML. It allows greater control of web page
behavior than HTML does alone. Lets start comprehensive JavaScript Tutorial and enjoy.
JavaScript is used in web sites to improve the look of it, verification of forms, browsers
version detection, cookies and exception handling and much more, it is client side programming language.
Details are available in JavaScript Tutorial section.
Learn JavaScript with hundreds of Examples.
|
|
|
|
|
|
|
|
|
|
|
|
|
SQL (Structured Query Language)
is a computer language used to store, manipulate,
and retrieve data stored in databases. Learn SQL at Global GuideLine.
Almost all modern Relational Database Management Systems like MS SQL Server, Microsoft Access, MSDE, Oracle, IBM DB2, Sybase, MySQL, Postgres and Informix use SQL as standard database language.
Standards for SQL exist. However, the SQL that can be used on each one of the major RDBMS today is
in different flavors. In this
SQL Tutorial, such differences are noted where appropriate. All details are available
in the SQL Tutorial Section.
Click here to learn about SQL basics and developed your skills.
|
|
|
|
|
|
|
|
Webmaster Said:
Thank you.