Misleading Message: DBCC execution completed. If DBCC printed error messages, contact your system administrator
Recently, it was encountered that one of the jobs which was running fine since ages has started failing with below error message.
Executed as user: Domain\MSSQLServer. DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000] (Message 2528) SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022). The step failed.
So, as every DBA does, I went ahead and checked the job steps. I found that Job isn’t doing much. It was simply inserting SQL Server and SQL Server Agent logs into a table and then truncating the logs:
INSERT INTO MS_SQLerrorlog EXEC ('sp_readerrorlog') Go Exec msdb.dbo.sp_cycle_errorlog Go --Save current SQL Agent log INSERT INTO MS_SQLAgentlog EXEC ('sp_readerrorlog -1, 2') Go Exec msdb.dbo.sp_cycle_agent_errorlog Go
The error message was very ambiguous and misleading. So, I tried to run the script from Management Studio. Here I see a different message all together.
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: 32.
For this error message, I started my research and found that this problem comes when SQL Server agent process is not able to get exclusive lock on SQLAGENT.OUT file. So, restarting SQL Server agent really fixed this problem for me but I am still not convinced as how and why DBCC Error message came and how SQL Server Agent process couldn’t get exclusive lock on this file because Agent was running fine since a long time and so it Job.
I am still researching on this and trying to reproduce this issue. I will post my finding later in the same blog.
I hope this helps for now..