3

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.

2

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/

0

The first recommendation is to download and install Adam Machanic's sp_whoisactive stored procedure. It will give you some much more information than the query in your question. Examine the output column called sql_text and look for anything familiar. You could extract information from sql_text and then use another favorite (free) tool of mine which is RedGates SQL Search.

(SQL Search is a free add-in for SQL Server Management Studio and Visual Studio 2017, that lets you quickly search for SQL across your databases.)

0

Tks both of you for the answers, but we just accessed the log for SQL Server and we could see that, while the query was running, thousands of records like this was put on the log:

Date                  Source   Severity  Message
05/03/2017 16:02:43   spid26s  Unknown   The activated proc '[LakeSideLockLogger].[_LakeSide_DbTools_LockMonitorEvents]' running on queue 'msdb.LakeSideLockLogger._LakeSide_DbTools_LockQueue' output the following:  'The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.'

Looks like this have something to do with the sqlsolution deadlock detector we installed last year. For some reason, even after uninstalling it, it kept the database objects and they were being called from time to time. And looks like the only way to uninstall these objects are from the program itself, but the evaluation in our server has expired. So, we have removed the objects manually.

Well, lets wait too see if this will solve our problem.

Tks again

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.