Sql-server – SQL Server job stuck even after restart

sql serversql-server-2016sql-server-agent

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)

Best Answer

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%';

Related Solutions

Sql-server – Workaround for very long running query on the schema

It might help to update statistics on the system tables that are used by the query. Based on your STATISTICS IO output you would run the following code:

UPDATE STATISTICS sys.syssingleobjrefs WITH FULLSCAN;
UPDATE STATISTICS sys.sysidxstats WITH FULLSCAN;
UPDATE STATISTICS sys.sysschobjs WITH FULLSCAN;
UPDATE STATISTICS sys.sysobjvalues WITH FULLSCAN;
UPDATE STATISTICS sys.syscolpars WITH FULLSCAN;
UPDATE STATISTICS sys.sysclsobjs WITH FULLSCAN;
UPDATE STATISTICS sys.sysiscols WITH FULLSCAN;
UPDATE STATISTICS sys.sysscalartypes WITH FULLSCAN;

"Statistics on system tables and query performance" contains a reference to this technique.

Depending on your database updating statistics may not be sufficient. I created a test database with 10000 simple tables with one column each:

DECLARE 
@j INT = 0,
@create_table VARCHAR(1000);
BEGIN
    WHILE @j < 10000
    BEGIN
        SET @create_table = 'CREATE TABLE X_TABLE_' + CAST(@j AS VARCHAR) + ' (ID INT NULL)';

        EXEC (@create_table);

        SET @j = @j + 1;
    END;
END;

The query that you have should return 10k rows but it still hadn't finished after one minute. It had only returned 651 rows so it was on track to finish in 15 minutes.

One change that made a big difference on my database was enabling the legacy cardinality estimator. The query returned all 10000 rows in one second after adding the following hint:

OPTION (RECOMPILE, USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION') );

As you said you cannot modify the code so that isn't immediately helpful. However, you can enable trace flag 9481 at the session level to get the legacy CE. It's possible to enable trace flag 9481 for a particular user as described in "Wanting your non-sysadmin users to enable certain trace flags without changing your app?". However, I do not know what other effects this would have on your application.

If it turns out that the legacy CE also improves performance in your database a less disruptive option may be to create a plan guide for this query. A plan guide will force a query to have a particular execution plan. You could capture a good enough execution play using the legacy CE and force future query executions to use that same plan even without TF 9481 turned on. You should be aware that many people in the industry do not speak favorably of plan guides.

Sql-server – Unexplainable DBCC CHECKDB Command Output

The error message you're seeing:

Sqlcmd: Warning: The last operation was terminated because the user pressed CTRL+C.

Occurs when sqlcmd is asked to quit while it is waiting for a response from SQL Server. Most likely someone (or something) killed the spawned cmd.exe process that was running SQLCMD.exe, which resulting in that error message being directed to the SQL Server errorlog.

Instead of using SQLCMD to execute DBCC CHECKDB, you should simply setup a standard T-SQL job step to run the DBCC CHECKDB command directly.

Alternately, run Ola Hallengren's excellent free and open-source Maintenance Solution, or one of the other myriad solutions available.

Best Answer

Related Solutions

Sql-server – Workaround for very long running query on the schema

Sql-server – Unexplainable DBCC CHECKDB Command Output

Related Question