Sql-server – Unexplainable DBCC CHECKDB Command Output

dbccdbcc-checkdbsql serversql-server-2017

Microsoft SQL Server 2017 (RTM-CU14-GDR) (KB4494352) – 14.0.3103.1
(X64) Mar 22 2019 22:33:11 Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Windows Server 2016 Datacenter 10.0
(Build 14393: ) (Hypervisor)

My Production server executes these command inside a SQL Agent job to output results to a table in my Administration database:

DECLARE @SQL VARCHAR(MAX) = 'master..xp_cmdshell ''SQLCMD -E -Q"DBCC CHECKDB('+@Database+') WITH NO_INFOMSGS, ALL_ERRORMSGS"'''; -- If all is well, there will be no output.
INSERT INTO DBCC_CHECKDB_Results EXEC(@SQL);

Last night, one line of information was output and the job stopped executing, with no errors shown in the job history. The one line of information was:

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

How is this possible? At 10:00 p.m. Sunday night, no one is logged onto the server. CTRL+C is not in the command line.

As a test, I executed this command, and while it was executing, typed: CTRL+C:

USE ARC 
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS:
Nothing happened.

Any ideas on what would cause that line of output?

Thanks, Lee

Best Answer

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.

Related Question