Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: how do I kill a -3 SPID?

There's plenty of information about positive SPIDs and even -1 and -2 but I haven't found any information on Blocked By -3. Would anyone be able to help?

GUID is all 0's, and when I run sp_who2 on MASTER db it shows a SPID of 56 as RUNNABLE on the tempdb but when I try to kill it or run sp_who2 on tempdb it doesn't show up, while the MASTER and tempdb sp_who2 instances are both SPID 54.

Edit: When running SELECT * FROM sys.sysprocesses WHERE spid = 56 this is the output for the SPID that's getting blocked:

spid    56
kpid    10500
blocked -3
waittype    0x0006
waittime    313816
lastwaittype    LCK_M_IS                        
waitresource    TAB: 5:1668253048:0                                                                                                                                                                                                                                             
dbid    5
uid 1
cpu 0
physical_io 0
memusage    4
login_time  02:44.3
last_batch  02:44.5
ecid    0
open_tran   0
status  suspended                     
sid 0x0105000000000005150000003DBE35AE805F26A82A34E78AE903000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
hostname    DESKTOP-JOS7UMG                                                                                                                 
program_name    Microsoft SQL Server Management Studio - Query                                                                                  
hostprocess 11364
cmd SELECT
net_library LPC                                                                                                
context_info    0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
sql_handle  0x01000500C609543A909F01271002000000000000
stmt_start  124
stmt_end    356
request_id  0

Edit2: When running select req_transactionUOW from master..syslockinfo where req_spid = [SPID Number] and KILL '[UOW Number]', the output req_transationUOW comes up all 0's and I get the following error when killing it:

The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.

Edit 3: When I run select * from sys.dm_exec_requests the below row is the output. Anything in here I can kill? SPID doesn't unblock the table and I don't want to go killing things all willy-nilly in case it crashes the server.

session_id  59
request_id  0
start_time  10:23.4
status  suspended
command SELECT
sql_handle      0x020000007509B2241E0ED80CA5FE2A1542C26EF873795AB30000000000000000000000000000000000000000
statement_start_offset  0
statement_end_offset    68
plan_handle 0x060005007509B224D06FB16B1002000001000000000000000000000000000000000000000000000000000000
database_id 5
user_id 1
connection_id   E63659D9-A41B-4FAE-89B2-6026AE51B938
blocking_session_id -3
wait_type   LCK_M_IS
wait_time   4246
last_wait_type  LCK_M_IS
wait_resource   OBJECT: 5:1668253048:0 
open_transaction_count  0
open_resultset_count    1
transaction_id  374312
context_info    0x
percent_complete    0
estimated_completion_time   0
cpu_time    0
total_elapsed_time  4247
scheduler_id    3
task_address    0x0000021236EC8108
reads   0
writes  0
logical_reads   2
text_size   2147483647
language    us_english
date_format mdy
date_first  7
quoted_identifier   1
arithabort  1
ansi_null_dflt_on   1
ansi_defaults   0
ansi_warnings   1
ansi_padding    1
ansi_nulls  1
concat_null_yields_null 1
transaction_isolation_level 2
lock_timeout    -1
deadlock_priority   0
row_count   1
prev_error  0
nest_level  0
granted_query_memory    0
executing_managed_code  0
group_id    2
query_hash  0x496ED8C805DE7CAA
query_plan_hash 0x36444CF46922D91E
statement_sql_handle    NULL
statement_context_id    NULL
dop 1
parallel_worker_count   NULL
external_script_request_id  NULL
like image 681
plankton Avatar asked Aug 19 '17 05:08

plankton


People also ask

How do I force kill a SPID in SQL Server?

Terminate running database backup using a KILL SPID command While the backup is in progress, execute the sp_who2 command to see the backup database process and note down the SPID for it. Let's assume we want to cancel the backup. Execute the KILL SPID command, and it immediately kills the backup process.

How do you kill a query with SPID?

Scroll down to the SPID of the process you would like to kill. Right click on that line and select 'Kill Process'. A popup window will open for you to confirm that you want to kill the process. Once this is done, the process will be terminated and all uncompleted transactions will begin the rollback process.

Can't kill a SPID in SQL?

Killing the spid is still going to wait for the rollback. If you kill the entire database process or reboot the server, when you restart it, it will still need to do the rollback. If you are happy to restore your last backup that is probably your fastest fix, otherwise you just have to wait.


1 Answers

SPID -3 is a deferred transaction from what I was able to find online. To kill it:

  1. Find the UOW Number

    select req_transactionUOW from master..syslockinfo where req_spid = [SPID Number]

  2. Copy the UOW number from step one

    KILL '[UOW Number]'

This will kill the negative SPID to resolve the issue.

like image 161
UnhandledExcepSean Avatar answered Oct 12 '22 07:10

UnhandledExcepSean