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
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.
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.
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.
SPID -3 is a deferred transaction from what I was able to find online. To kill it:
Find the UOW Number
select req_transactionUOW from master..syslockinfo where req_spid = [SPID Number]
Copy the UOW number from step one
KILL '[UOW Number]'
This will kill the negative SPID to resolve the issue.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With