Sql-server – Encrypted Query locking server from time to time

sql serversql-server-2008

We have a system that runs without problems for days, but from time to time we see that all the queries do not run at all, and at the same time the SQL server is consuming too much CPU

So when we execute the following query to get all the running queries on the server:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

We see the following running query:

TEXT    session_id  status      command     cpu_time    total_elapsed_time
NULL    26          background  INSERT      494039      1362613

Executing sp_who2:

SPID    Status      Login   HostName    BlkBy   DBName  Command CPUTime DiskIO  LastBatch       ProgramName SPID    REQUESTID
26      BACKGROUND  sa        .           .     msdb    SELECT  504257  4152076 04/26 12:16:13              26      0

So, how can I find what proccess, job, task, or whatever is executing this query and what exactly it is doing?

Tks in advance.

Best Answer

Looking at what you posted, it's most likely an Agent job trying to insert data into a table in msdb clashing with an Agent job trying to select data.

This can take a long time, especially if you've never cleaned up Agent job history. These tables can get quite large and contentious on old or busy servers.

Have a look here (full disclosure, it's my company's blog): https://www.brentozar.com/blitz/msdb-history-not-purged/

Related Question