March 1, 2012 at 4:46 pm
i performed a kill operation on a long running spid ---
but i am still recieving :
SPID 828: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.
How to check the current status for the kill other than using kill 828 with statusonly
Pls suggest .
March 1, 2012 at 6:53 pm
Actually, it sounds like you may have another active SPID that the KILL/ROLLBACK is waiting for. This can sometimes happen with things like dynamic SQL or a call to an external program like when you use xp_CmdShell. You need to find that other SPID an kill it before the KILL/ROLLBACK will start on the first SPID you identified.
--Jeff Moden
March 1, 2012 at 9:36 pm
This is also typical behavior when the query/statement you killed spans across a "linked server". If your query does perform TSQL on a linked server, you need to find the spid on the linked server and kill it there as well. Voila. After killing it on the LS it will typically rolled back within a few seconds. I am a fan of sp_whom2...so I run that to get the remote spid for the "actual" query I need to kill...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
March 1, 2012 at 11:48 pm
check the cpu or physical IO is moving or not of that spid which is in killed\rollback state ?
March 2, 2012 at 3:03 am
It is possible to get a hung spid in this state which will never end. The only way to get rid of it is to restart the service.
March 2, 2012 at 6:34 am
MysteryJimbo (3/2/2012)
It is possible to get a hung spid in this state which will never end. The only way to get rid of it is to restart the service.
That's the condition I was talking about. Normally, another SPID is involved and if you can find that one and kill it, you won't have to restart the service.
--Jeff Moden
March 2, 2012 at 6:45 am
Yeh It is possible to get a hung spid in this state which will never end.
March 2, 2012 at 6:55 am
MyDoggieJessie (3/1/2012)
This is also typical behavior when the query/statement you killed spans across a "linked server". If your query does perform TSQL on a linked server, you need to find the spid on the linked server and kill it there as well. Voila. After killing it on the LS it will typically rolled back within a few seconds. I am a fan of sp_whom2...so I run that to get the remote spid for the "actual" query I need to kill...
sp_whom2?
---------------------------------------------------------------------
March 2, 2012 at 7:33 am
Yes "sp_whom2" a modified/better version of "sp_who"
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_whom2] Script Date: 03/02/2012 08:30:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [dbo].[sp_whom2] --- 1995/11/03 10:16
@SPID varchar(max) = NULL,
@LOGINAMEsysname = NULL,
@sortnvarchar(50)= NULL, --Spid, BlkBy, Status, Login, HostName, ProgramName, JobName, StepID, CPUTime, DiskIO,
--LastBatch, DBName, CMD, LastWaitType, WaitResource, Wait, MemUsage, OpenTran, HostProcess
@USERsysname= NULL,
@DBNamesysname = NULL,
@HOSTNAMEnvarchar(50)= NULL,
@JobNamenvarchar(400)= NULL,
@Filter tinyint = NULL
AS
/* ############################################################################################################### */
--Processes Running
/* ############################################################################################################### */
/*
----------------------------------------------------------------------------------------------------------------
Purpose :To pull any runnable or blocking processes
Department:DBA
Created For:DBA
----------------------------------------------------------------------------------------------------------------
NOTES:This is a slight modification of the SP_Who SP, modified by MFrank, so that it displays
information that is actually needed, and cutting back on the amount of junk displayed.
System processes and inactive processes are not displayed. Column formatting has
been modified to display most relevant columns on one screen. Most of the modifications
were made towards the end of the SP.
----------------------------------------------------------------------------------------------------------------
Created On:11/03/1995
Create By :MFrank
----------------------------------------------------------------------------------------------------------------
Modified On:11/15/2002
Modified By:DJB
Changes :1.Added job names and inputbuffer information.
----------------------------------------------------------------------------------------------------------------
Modified On:06/11/2003
Modified By:DJB
Changes :1. Added sort clause
2. Changed layout of result set.
----------------------------------------------------------------------------------------------------------------
Modified On:01/15/2010
Modified By:DJB
Changes :1. Modified to work with SQL Server 2k5 where everyone can use it. Must run these:
--GRANT SELECT ON msdb.dbo.sysjobsteps TO [public]
--GRANT SELECT ON msdb.dbo.sysjobs TO [public]
And any specific groups that need VIEW SERVER STATE
----------------------------------------------------------------------------------------------------------------
Modified On:12/22/2010
Modified By:SJM
Changes :1. Added FilterCols variable to allow for the procedure to bring back only the chosen
columns in the resultset - Ex: sp_whom2 @Filter=1
----------------------------------------------------------------------------------------------------------------
Modified On:10/04/2011
Modified By:DJB
Changes :1. Mofidified the command text field in the ##tb1_sysprocesses table to allow MAX to prevent
overflow errors.
2. Modified to follow standards template
3. Added wait column
4. Fixed the sort features so that each column can be sorted appropriately (numeric was not
sorting correctly due to returning as a varchar.
----------------------------------------------------------------------------------------------------------------
EXEC sp_whom2 @sort = 'CPUTime DESC'
*/
SET NOCOUNT ON
--MANAGE TABLES
BEGIN
IF (OBJECT_ID('tempdb..##tb1_sysprocesses') IS NOT NULL)
BEGIN
DROP TABLE ##tb1_sysprocesses
END
CREATE TABLE ##tb1_sysprocesses
(
spid smallint NOT NULL,
status nvarchar(1500) NOT NULL,
sid binary(86) NOT NULL,
blocked smallint NOT NULL,
DBName nvarchar(1500) NULL,
loginname nvarchar(1500) NULL,
hostname nvarchar(1500) NOT NULL,
[program_name] nvarchar(1500) NOT NULL,
cmd_text nvarchar(MAX) NOT NULL,
last_batch_char varchar(1500) NULL,
cmd nvarchar(1500) NULL,
cpu int NOT NULL,
DiskIO bigint NOT NULL,
memusage int NULL,
lastwaittype nvarchar(1500)NULL,
waitresource nvarchar(1500) NULL,
open_tran smallintNULL,
wait int NULL,
hostprocess nvarchar(1500)NULL,
spid_sort smallint NOT NULL
)
IF (OBJECT_ID('tempdb..#JobInfo') IS NOT NULL)
BEGIN
DROP TABLE #JobInfo
END
CREATE TABLE #JobInfo
(
spid smallint
,JobID uniqueidentifier NULL
,StepID int NULL
)
END
--MANAGE VARIABLES
BEGIN
DECLARE @retcode int
DECLARE @sidlow varbinary(85)
DECLARE @sidhigh varbinary(85)
DECLARE @sid1 varbinary(85)
DECLARE @SPID2 varchar(MAX)
DECLARE @SPID2low int
DECLARE @SPID2high int
DECLARE @JobID varchar(max)
DECLARE @StepID varchar(max)
DECLARE @ExecSql nvarchar(max)
DECLARE @charMaxLenLoginName varchar(max)
DECLARE @charMaxLenDBNamevarchar(max)
DECLARE @charMaxLenCPUTime varchar(max)
DECLARE @charMaxLenDiskIO varchar(max)
DECLARE @charMaxLenHostName varchar(max)
DECLARE @charMaxLenProgramName varchar(max)
DECLARE @charMaxLenLastBatch varchar(max)
DECLARE @charMaxLenCommand varchar(max)
DECLARE @charMaxLenJobName varchar(max)
DECLARE @charMaxLenLastWaitTypevarchar(max)
DECLARE @charMaxLenWaitResourcevarchar(max)
DECLARE @charsidlow varchar(max)
DECLARE @charsidhigh varchar(max)
DECLARE @charspidlow varchar(max)
DECLARE @charspidhigh varchar(max)
END
--SET VARIABLES
BEGIN
SELECT @retcode = 0 -- 0=good ,1=bad.
--------defaults
SELECT @sidlow = CONVERT( varbinary(85), (REPLICATE(char(0), 85)))
SELECT @sidhigh = CONVERT( varbinary(85), (REPLICATE(char(1), 85)))
SELECT
@SPID2low = 0,
@SPID2high = 32767
SELECT @sid1 = NULL
END
/* ######################################### START MAIN PROCEDURE HERE ########################################## */
BEGIN
IF (@LOGINAME IS NULL) --Simple default to all LoginNames.
GOTO LABEL_17PARM1EDITED
--SET OTHER VARIABLES
BEGIN
IF EXISTS(SELECT 1
FROM master.dbo.syslogins
WHERE loginname = @LOGINAME)
BEGIN
SELECT @sid1 = sid
FROM master.dbo.syslogins
WHERE loginname = @LOGINAME
END
IF (@sid1 IS NOT NULL) --Parm is a recognized login name.
BEGIN
SELECT
@sidlow = suser_sid(@LOGINAME),
@sidhigh = suser_sid(@LOGINAME)
GOTO LABEL_17PARM1EDITED
END
IF (lower(@LOGINAME) IN ('active')) --Special action, not sleeping.
BEGIN
SELECT @LOGINAME = lower(@LOGINAME)
GOTO LABEL_17PARM1EDITED
END
IF (patindex ('%[^0-9]%' , isnull(@LOGINAME,'z')) = 0) --Is a number.
BEGIN
SELECT
@SPID2low = convert(int, @LOGINAME),
@SPID2high = convert(int, @LOGINAME)
GOTO LABEL_17PARM1EDITED
END
RAISERROR(15007,-1,-1,@LOGINAME)
SELECT @retcode = 1
GOTO LABEL_86RETURN
END
LABEL_17PARM1EDITED:
-------------------- Capture consistent sysprocesses. -------------------
IF @SPID IS NOT NULL
BEGIN
INSERT INTO ##tb1_sysprocesses
(
spid,
[status],
[sid],
blocked,
DBName,
loginname,
hostname,
[program_name],
cmd_text,
last_batch_char,
cmd,
cpu,
DiskIO,
memusage,
lastwaittype,
waitresource,
open_tran,
wait,
hostprocess,
spid_sort
)
SELECT
sp.spid,
sp.[status],
[sid],
sp.blocked,
DB_NAME(sp.[dbid]),
sp.loginame,
sp.hostname,
sp.[program_name],
SUBSTRING(st.[text], (sp.stmt_start/2) + 1,
((CASE sp.stmt_end
WHEN -1 THEN DATALENGTH(st.[text])
ELSE sp.stmt_end
END - sp.stmt_start)/2) + 1) AS statement_text,
SUBSTRING( CONVERT(varchar, sp.last_batch, 111) ,6 ,5 )
+ ' '
+ SUBSTRING( CONVERT(varchar, sp.last_batch, 113) ,13 ,8 ),
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.lastwaittype,
waitresource,
open_tran,
'', --wait
hostprocess,
spid
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
WHERE program_name NOT IN ('DatabaseMail90 - Id<3424>')
AND spid = @SPID
END
ELSE
BEGIN
INSERT INTO ##tb1_sysprocesses
(
spid,
[status],
[sid],
blocked,
DBName,
loginname,
hostname,
[program_name],
cmd_text,
last_batch_char,
cmd,
cpu,
DiskIO,
memusage,
lastwaittype,
waitresource,
open_tran,
wait,
hostprocess,
spid_sort
)
SELECT
sp.spid,
sp.[status],
[sid],
sp.blocked,
DB_NAME(sp.[dbid]),
sp.loginame,
sp.hostname,
sp.[program_name],
SUBSTRING(st.[text], (sp.stmt_start/2) + 1,
((CASE sp.stmt_end
WHEN -1 THEN DATALENGTH(st.[text])
ELSE sp.stmt_end
END - sp.stmt_start)/2) + 1) AS statement_text,
SUBSTRING(CONVERT(varchar, sp.last_batch, 111) ,6 ,5 )
+ ' '
+ SUBSTRING(CONVERT(varchar, sp.last_batch, 113) ,13 ,8 ) ,
sp.cmd,
sp.cpu,
sp.physical_io,
sp.memusage,
sp.lastwaittype,
waitresource,
open_tran,
'', --wait
hostprocess,
spid
FROM sys.sysprocesses sp
CROSS APPLY sys.dm_exec_sql_text(sp.sql_handle) AS st
WHERE [program_name] NOT IN ('DatabaseMail90 - Id<3424>')
END
UPDATE ##tb1_sysprocesses
SET wait = ISNULL(wait_duration_ms, 0)
FROM sys.dm_os_waiting_tasks wt
WHERE wt.session_id = spid
--------Screen out any rows?
DELETE ##tb1_sysprocesses
WHERE status IN
(
'sleeping',
'BACKGROUND'
)
AND UPPER(cmd) IN
(
'AWAITING COMMAND',
'CHECKPOINT SLEEP',
'LAZY WRITER',
'LOCK MONITOR',
'LOG WRITER',
'MIRROR HANDLER',
'RA MANAGER',
'SIGNAL HANDLER',
'TASK MANAGER'
)
AND blocked = 0
/*Load cursor with spids and with potential job references*/
BEGIN
DECLARE jobs_cursor INSENSITIVE CURSOR FOR
SELECT DISTINCT
spid,
CASE
WHEN [program_name] IS NOT NULL
AND SUBSTRING([program_name], 1, 8) = 'SQLAgent'
AND PATINDEX('%TSQL JobStep (Job %', [program_name]) > 0
THEN SUBSTRING([program_name], PATINDEX('%TSQL JobStep (Job %', [program_name])
+ 18, PATINDEX('% : Step %', [program_name])
- (patindex('%TSQL JobStep (Job %', [program_name])
+ 18))
ELSE NULL
END,
CASE
WHEN [program_name] IS NOT NULL
AND SUBSTRING([program_name], 1, 8) = 'SQLAgent'
AND PATINDEX('%TSQL JobStep (Job %',[program_name]) > 0
THEN SUBSTRING([program_name], PATINDEX('% : Step %', [program_name])
+ 8, PATINDEX('%)%', [program_name])
- (PATINDEX('% : Step %', [program_name])
+ 8))
ELSE NULL
END
FROM ##tb1_sysprocesses
FOR READ ONLY
OPEN jobs_cursor
FETCH NEXT FROM jobs_cursor
INTO
@SPID2,
@JobID,
@StepID
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @ExecSql = 'SELECT ' + @SPID2 + ', '
IF @JobID IS NULL
BEGIN
SELECT @ExecSql = @ExecSql + 'null, '
END
ELSE
BEGIN
SELECT @ExecSql = @ExecSql + @JobID + ', '
END
IF @StepID IS NULL
BEGIN
SELECT @ExecSql = @ExecSql + 'null'
END
ELSE
BEGIN
SELECT @ExecSql = @ExecSql + @StepID
END
INSERT #JobInfo
(
spid,
JobID,
StepID
)
EXEC (@ExecSql)
SELECT @ExecSql = NULL
FETCH NEXT FROM jobs_cursor
INTO
@SPID2,
@JobID,
@StepID
END
CLOSE jobs_cursor
DEALLOCATE jobs_cursor
END
--------Prepare to dynamically optimize column widths.
SELECT
@charsidlow= convert( varchar(85), @sidlow),
@charsidhigh= convert( varchar(85), @sidhigh),
@charspidlow= convert( varchar, @SPID2low),
@charspidhigh= convert( varchar, @SPID2high)
SELECT
@charMaxLenLoginName = CONVERT(varchar, ISNULL(MAX(DATALENGTH(loginname)), 5)),
@charMaxLenDBName = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, DBName))), 6)),
@charMaxLenCPUTime = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, cpu))), 7)),
@charMaxLenDiskIO = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, DiskIO))), 6)),
@charMaxLenCommand = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, cmd))), 7)),
@charMaxLenHostName = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, hostname))), 15)),
@charMaxLenProgramName = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, [program_name]))), 255)),
@charMaxLenLastBatch = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, last_batch_char))), 9)),
@charMaxLenLastWaitType = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, lastwaittype))), 20)),
@charMaxLenWaitResource = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, waitresource))), 20))
FROM ##tb1_sysprocesses
WHERE spid >= @SPID2low
AND spid <= @SPID2high
SELECT @charMaxLenJobName = CONVERT(varchar, ISNULL(MAX(DATALENGTH(CONVERT(varchar, name))), 50))
FROM msdb..sysjobs
--------Output the report.
-- sp_whom2 @filter=1
/*** This is the area that was modified by MFrank the most. Exclusion for system processes included, and datalengths were modified. */
IF (@Filter = 1)
BEGIN
SET @ExecSql = 'SET NOCOUNT OFF
SELECT DISTINCT
SPID = CONVERT(char(5), s.spid)
+ ''[''
+ RTRIM(CONVERT(char(5), s.blocked))
+ '']'' ,
HostName =
CASE s.hostname
WHEN NULL THEN '' ''
ELSE CONVERT(varchar(15), SUBSTRING(s.hostname, 1,' + @charMaxLenHostName + '))
END,
JobName = RTRIM(SUBSTRING( sj.name, 1, ' + @charMaxLenJobName + ')) + '' ['' + RTRIM(CONVERT(CHAR(3), ss.step_id)) + '']'',
LastBatch = SUBSTRING( s.last_batch_char, 1, ' + @charMaxLenLastBatch + '),
DBName = SUBSTRING( DBNAME, 1, ' + @charMaxLenDBName + '),
CMD_TEXT =
CASE
WHEN s.cmd_text IS NOT NULL THEN s.cmd_text
ELSE SUBSTRING(s.cmd, 1, ' + @charMaxLenCommand + ')
END,
CAST(CONVERT(varchar(10), Wait) AS int),
s.HostProcess
FROM ##tb1_sysprocesses s --Usually DB qualification is needed in exec().
LEFT JOIN #JobInfo f ON
s.spid = f.spid
LEFT JOIN msdb.dbo.sysjobs sj WITH(READUNCOMMITTED) ON
f.JobId = sj.job_id
LEFT JOIN msdb.dbo.sysjobsteps ss WITH(READUNCOMMITTED) ON
sj.job_id = ss.job_id
AND ss.step_id = f.StepId
WHERE s.spid >= ' + @charspidlow + '
AND s.spid <= ' + @charspidhigh + ' '
END
ELSE
BEGIN
SET @ExecSql = 'SET NOCOUNT OFF
SELECT DISTINCT
SPID = CAST(CONVERT(char(5), s.spid) AS int),
BlkBy = CAST(s.blocked AS int),
Status =
CASE LOWER( s.status)
WHEN ''sleeping'' THEN CONVERT(varchar(10), lower(s.status))
ELSE CONVERT(varchar(10), upper(s.status))
END,
Login = CONVERT(varchar(30), SUBSTRING(s.loginname, 1, ' + @charMaxLenLoginName + ')),
HostName =
CASE s.hostname
WHEN NULL THEN '' ''
ELSE CONVERT(varchar(15), SUBSTRING(s.hostname, 1, ' + @charMaxLenHostName + '))
END,
ProgramName = SUBSTRING(s.program_name, 1, ' + @charMaxLenProgramName + '),
JobName = SUBSTRING(sj.name, 1, ' + @charMaxLenJobName + '),
StepID = CAST(ss.step_id AS int),
CPUTime = CAST(SUBSTRING(CONVERT(varchar, s.cpu), 1, ' + @charMaxLenCPUTime + ') AS int),
DiskIO = CAST(SUBSTRING(CONVERT(varchar, s.DiskIO), 1, ' + @charMaxLenDiskIO + ') AS int),
LastBatch= SUBSTRING(s.last_batch_char, 1, ' + @charMaxLenLastBatch + '),
DBName = SUBSTRING(DBNAME, 1, ' + @charMaxLenDBName + '),
CMD_TEXT =
CASE
WHEN s.cmd_text IS NOT NULL THEN s.cmd_text
ELSE SUBSTRINg( s.cmd, 1, ' + @charMaxLenCommand + ')
END,
CMD,
LastWaitType = SUBSTRING(s.LastWaitType, 1, ' + @charMaxLenLastWaitType + '),
WaitResource = SUBSTRING(s.WaitResource, 1, ' + @charMaxLenWaitResource + '),
Wait = CAST(Wait AS int),
MemUsage = CAST(s.MemUsage AS int),
OpenTran = CAST(s.Open_Tran AS int),
HostProcess = CAST(s.HostProcess AS int),
SPIDR = CAST(CONVERT(char(5), s.spid) AS int) --Handy extra for right-scrolling users.
FROM ##tb1_sysprocesses s --Usually DB qualification is needed in exec().
LEFT JOIN #JobInfo f ON s.spid = f.spid
LEFT JOIN msdb.dbo.sysjobs sj WITH(READUNCOMMITTED) ON f.JobId = sj.job_id
LEFT JOIN msdb.dbo.sysjobsteps ss WITH(READUNCOMMITTED) ON sj.job_id = ss.job_id
AND ss.step_id = f.StepId
WHERE s.spid >= ' + @charspidlow + '
AND s.spid <= ' + @charspidhigh + ' '
END
--SET Filters
BEGIN
IF @LOGINAME IS NOT NULL
BEGIN
SELECT @ExecSql = @ExecSql + '
AND s.loginname LIKE ''%' + @LOGINAME + '%'''
END
IF @HOSTNAME IS NOT NULL
BEGIN
SELECT @ExecSql = @ExecSql + '
AND s.hostname LIKE ''' + @HOSTNAME + '%'''
END
IF @DBName IS NOT NULL
BEGIN
SELECT @ExecSql = @ExecSql + '
AND s.DBName LIKE ''' + @DBName + '%'''
END
IF @JobName IS NOT NULL
BEGIN
SELECT @ExecSql = @ExecSql + '
AND SUBSTRING(sj.name, 1, ' + @charMaxLenJobName + ') LIKE ''' + @JobName + '%'''
END
IF @sort IS NOT NULL
BEGIN
SELECT @ExecSql = @ExecSql + '
ORDER BY ' + @sort + ''
END
END
EXECUTE sp_executesql @ExecSql
--PRINT @ExecSql
LABEL_86RETURN:
END
IF (OBJECT_ID('tempdb..##tb1_sysprocesses') IS NOT NULL)
DROP TABLE ##tb1_sysprocesses
IF (OBJECT_ID('tempdb..#JobInfo') IS NOT NULL)
DROP TABLE #JobInfo
--return @retcode -- sp_whom2
GO
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply