Not an original script but tweaked that might get where you need to be or at least a start:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[uspKillUsersFETCH] @dbname varchar(50)
as
DECLARE @strSQL varchar(255)
PRINT 'Killing Users '
PRINT '-------------------------------------------------------------------------------------------'
CREATE table #tmpUsers(
spid int,
dbname varchar(128),
cmd varchar(128))
INSERT INTO #tmpUsers
select spid, convert(varchar(128),db_name(dbid)), cmd
from master.dbo.sysprocesses (nolock)
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, program_name FROM #tmpUsers WHERE dbname = 'database name here'
and spid > 50
and cmd like '%FETCH API_CURSORXXX%'
DECLARE @spid int
DECLARE @dbname2 varchar(128)
OPEN LoginCursor
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT 'Killing ' + convert(varchar(10),@spid)
SET @strSQL = 'KILL ' + convert(varchar(10),@spid)
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END
CLOSE LoginCursor
DEALLOCATE LoginCursor
DROP table #tmpUsers
****Again, not original so credit must given to that now-unknown script writer ****
I use a version of this to kill SQL 2005 spids that connect to a log-shipped stand-by database using Mgt Studion
Pulling the power causes everything to stop in flight, with no warning. kill -9 has the same effect on a single process, forcefully terminating it with a SIGKILL.
If a process is killed by kernel or power outage, it doesn't do any clean-up. That means you could have half-written files, inconsistent states, or lost caches. You usually don't have to worry about any of this because of journaling, exit status and battery backup.
Temporary files in /tmp will be automatically gone if they are in tmpfs, but you may still have application-specific lock files laying around to remove, like the lock and .parentlock for firefox.
Most software is smart enough to retry a transaction if it doesn't record a successful exit status. A good example of this is a typical mail system. If a message is being delivered, but gets cut off in the middle, the sender will retry later until it gets a success.
Your filesystem is probably journaled. If you are moving or writing a file and it dies mid-stream, the journaled file system will still reference the original. The journaled filesystem will make changes non-destructively, leaving the old copy, then only reference the new copy as a last step before reclaiming space the old copies occupied on disk.
Now if you have a RAID array, it has all kinds of memory buffers to increase performance and provide reliability in a power failure. Most likely your filesystem will not know about the caches in the device and their state, so it thinks a change has been committed to disk, but it is still in the RAID cache somewhere. So what happens when the power dies? Hopefully you have a functional battery in your RAID enclosure and you monitor it. Otherwise you have a corrupt file system to fsck.
Yes, a few bits can become corrupted in a binary, but I would not worry about that much on modern hardware. If you are really paranoid, you can monitor the health of your disks and RAID with the appropriate tools, but you should be doing that anyway. Do regular backups and get an Uninterruptible Power Supply.
Best Answer
When you are killing a process with a
kill pid
, you are sending a SIGTERM. Sometimes a process is stuck in a state where it won't listen to signals. When that happens, trykill -9 pid
and that will probably kill it for good.In this case, the defunct process (31217) won't be able to be killed, but the parent process (31216) should die and take its child process with it.