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
A -3 spid is a "deferred transaction".
During database recovery SQL Server encountered an active transaction that it was unable to roll back due to some IO issue. So the locks that are taken for this transaction during the database recovery are never released and are associated with session -3
You can't kill this, you need to resolve the underlying issue. See "Moving a Transaction Out of the DEFERRED State" for resolutions to this. These are bullet pointed below to give a quick overview and some protection against link rot but...
⚠️ Caution! Some of these can cause data loss! See the linked article for important additional details before attempting this
Paul Randal gives an example of how to engineer a deferred transaction here.