3

I have a SQL Server Agent job stuck in "In Progress". It's a FULL backup that usually takes 30 minutes, but it's taking 7 hours and counting. I've restarted the Agent and then the whole OS and it still says "In Progress".

Is there any way to stop that?

If I run select * from sys.dm_exec_requests, I get 2 rows in "running" status, one says "SELECT", the other one "DBCC TABLE CHECK". And indeed the first step of the job is to run a Database Integrity check using Ola Hallengren's scripts.

Version:

Microsoft SQL Server 2016 (RTM-GDR) (KB4019088) - 13.0.1742.0 (X64)
Jul 5 2017 23:41:17 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 6.3 (Build 14393: ) (Hypervisor)

6

Not using the GUID but using the T-SQL procedure:

sp_stop_job

Could help resolve issues like the one you are having.

Example:

exec msdb.dbo.sp_stop_job @job_name = 'enterjobnamehere'

Checking the current running queries for any queries started by the agent and killing these could also help.

An example of adapting the script to find running queries started by the agent service:

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
INNER JOIN sys.dm_exec_Sessions s
on req.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext
WHERE [Program_Name] like 'SQLAgent%';

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.