July 17, 2008 at 2:57 am
Hi!!
Help me out with query :
--Here am inserting into spid1 table after selecting
the required spid--------------
INSERT INTO spid1
select p.spid from master..sysprocesses p,master..sysdatabases d where
p.dbid = d.dbid and d.name like 'BIN%'
--------------------------------------------------------------
---Next I want to have a loop to kill the spids taken from spid1 table--
Let me know how to do!!
thanks,
🙂
July 17, 2008 at 3:13 am
Thanks got it 🙂
Here’s a script, for killing a SPIDs against a particular database, here am using BIN as the database.
We have to change the name of the database accordingly.
This script can be used when we need to check for blockings on a database and helpful, when we have to restore a database especially when the database is in use while restore.
---------------------------------------------------------------
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 p.spid from master..sysprocesses p,master..sysdatabases d where
p.dbid = d.dbid and d.name like 'BIN%'
ORDER BY p.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
July 17, 2008 at 3:22 am
rinu,
Nice script...:),
you can send your article to this forum.
so if you have any new and different idea then you make it one article and post in this forum. So it will be useful for others.
Menu --> Write for us --> Getting Started --> click here
and submit the article.
Cheers!
Sandy.
--
July 17, 2008 at 3:35 am
thanks sandy!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply