Unexplainable DBCC CHECKDB Command Output

Question

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

• Randi Vertongen: Hi and welcome to dba.se. This is not an answer but I always encourage others to check out Ola Hallengren's Maintenance Solution. Apart from backups and index optimization it also covers integrity checks. The integrity checks can also be logged in a dbo.CommandLog table as long as you specify @LogToTable='Y'
• Shanky: Did you checked for deadlock, which might have occurred and caused CMD operation to rollback and this might have occurred via insert.

Answer 1

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.