Query Store Issue or Totally Unrelated - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Query Store Issue or Totally Unrelated

sql-server sql-server-2016 add tag

John K N

After turning on QueryStore for a database on a SQL Server 2016 instance I checked the `ERRORLOG`file and found the following error message:


>     2020-08-19 10:11:16.42 spid26s     Error: 1222, Severity: 16, State: 111.  
>     2020-08-19 10:11:16.42 spid26s     Lock request time out period exceeded.

When I execute 

    sp_who2 26

The SPID 26 is (currently) shown as:



    | SPID  |   Status    | Login | HostName | BlkBy | DBName |   Command    | CPUTime | DiskIO |   LastBatch    | ProgramName | SPID  | REQUESTID |  |
    |-------|-------------|-------|----------|-------|--------|--------------|---------|--------|----------------|-------------|-------|-----------|--|
    | 26    | sleeping    | sa    |        . |     . | master | TASK MANAGER |       0 |      0 | 08/19 11:32:14 |             | 26    |         0 |  |




The server was last booted at:

>      2020-06-28 23:37:17.94 Server      Microsoft SQL Server 2016 (SP2-CU11-GDR) (KB4535706) - 13.0.5622.0 (X64)   
>     	Dec 15 2019 08:03:11   
>     	Copyright (c) Microsoft Corporation  
>     	Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)  

Between the reboot and now, this error message has never occurred up until `2020-08-19 10:11:16.42`. 
  
I couldn't find any information in the **system_health** Extended Events regarding the event round about the same time.
  
If I recall correctly, then the spid 26 should be a system spid.
  
# Questions
  
- What is the spid 26s actually for?  
- How can I find out what happened?

Top Answer

Josh Darnell

Since the Query Store change you made succeeded, I think this lock timeout error is unrelated.

> How can I find out what happened?

I don't know of a way to figure out retroactively what action hit the lock timeout.  

In the future, you might be able to capture information about the process by keeping the `lock_timeout_greater_than_0` extended event session running.  To be clear, I haven't used that, so I can't recommend running it.  Definitely test in a non-prod environment before giving that a try.

> What is the spid 26s actually for?

As far as what spid 26 "TASK MANAGER" is, you're right that spids <50 are generally system tasks.  From what I can glean online, the "TASK MANAGER" command could handle a number of different things (from change tracking cleanup to async stat update background tasks).  But it's not officially documented, so it's hard to know for sure.

1 Answer