11

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
plankton
  • 369
  • 5
  • 21
  • what does this show `use master SELECT DISTINCT(request_owner_guid) as UoW_Guid FROM sys.dm_tran_locks WHERE request_session_id =-2` – TheGameiswar Aug 19 '17 at 05:53
  • @TheGameiswar There are no records in the output. – plankton Aug 19 '17 at 12:55
  • https://www.mssqltips.com/sqlservertip/4142/how-to-kill-a-blocking-negative-spid-in-sql-server/ does this article help you? – Jacob H Aug 21 '17 at 19:09
  • @JacobH I came across that article but sadly it's specific to a -2 `Block`. It looks like the current answer should work. – plankton Aug 21 '17 at 19:24
  • 1
    This might really belong [here](https://dba.stackexchange.com/) if you are just trying to kill the process outside of a programmatic approach. – SMM Aug 22 '17 at 19:42
  • 3
    So...here's the deal. Your question occupies a bit of a gray area for what's on-topic for Stack Overflow. I'm not an expert in the technology, and I asked around and got conflicting views. I can certainly refund your bounty and migrate this to [DBA.SE] for you, and everyone agrees it would be a great fit there. But, you wouldn't really have enough reputation on that other site to offer a bounty there, so it's up to you what you want me to do. I would be okay with leaving it here at least until the bounty ends, or I can move it. – Cody Gray - on strike Aug 25 '17 at 12:49
  • 1
    Definitely search the DBA site first, though, as [there are lots of similar questions there](https://dba.stackexchange.com/search?q=kill+spid+is%3Aquestion), and you might find your answer already exists! – Cody Gray - on strike Aug 25 '17 at 12:50
  • @CodyGray Apparently the DBA site is blocked at work so I'll have to search it when I get home tonight. Is it alright if I get back to you later? – plankton Aug 25 '17 at 13:07
  • No problem at all. – Cody Gray - on strike Aug 25 '17 at 13:09

2 Answers2

2

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

  • If it was deferred because a filegroup was offline, bring the filegroup back online.
  • Restart the database and hope the issue was transient.
  • Restore the database (potentially online)
  • mark filegroup as defunct
  • repair the database

Paul Randal gives an example of how to engineer a deferred transaction here.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845
-1

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.

UnhandledExcepSean
  • 12,504
  • 2
  • 35
  • 51
  • Sadly that doesn't seem to work. The 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.` – plankton Aug 21 '17 at 23:18
  • @plankton is that the only value you get? Also, check out this link: https://www.sqlservercentral.com/Forums/Topic1345288-391-1.aspx – UnhandledExcepSean Aug 22 '17 at 00:03
  • Yeah 2 rows of all 0's. That link seems to be focuses on -2 `Blocks` but I ran `select * from sys.dm_exec_requests` based on one of the replies and it looks like I might be able to use a decent amount of the information in there. Should I edit my post and throw in all the record information in there? I feel like I could find something to `kill` based on that. – plankton Aug 22 '17 at 00:13
  • @plankton if you have additional information that may get help solve the problem, updating your question would greatly help – UnhandledExcepSean Aug 22 '17 at 00:19