Job 'IntegrityCheck' failed

Question

Hello.
I am doing log shipping transaction from one primary database to 2 different secondary databases.
Then every morning I validate success by running the query (see attachment). My script checks that value of 'last_run_outcome' equal 1
I noticed that the value for Job 'IntegrityCheck' is 0.
I ran 'DBCC CHECKDB' against database and it completed successfully.
My question: could I disregard this failure in my query or need to troubleshoot?

Thanks51303-job-result.txt

Answer 1

So just above the error message about DBCC terminating abnormally, I see this being logged 80 ms earlier:

Setting database option SINGLE_USER to ON for database 'app4_starlims_data_c2'.

Assuming that this is done with ROLLBACK IMMEDIATE, this explains why DBCC terminated. I also made a test and verified that this operation results exactly in this message in the errorlog.

You talk about restore - you can of course not restore a database and the same time run DBCC on the database.

I checked that we set option 'disconnect' other users while restore database.

That is unlikely to be the correct action. The correct action would be to reschedule the job so that the RESTORE runs and completes before DBCC. There is little point in running on DBCC on something you will throw away just a few minutes later.

Answer 2

So did you right-click the job in Object Explorer, selected View History and looked at the output from the failing job step?

Answer 3

Cannot continue the execution because the session is in the kill state.

That is, the session was killed for some reason. That may be because someone executed the KILL command. It can also be due to some severe error which caused SQL Server to terminate the connection. It seems that the job only ran for three seconds, which makes it more likely that it was the latter. Even more so, since this appears to have been going for a few days.

Check the SQL Server errorlog. There should be a message shortly after midnight 2020-12-26. As there should be for the few nights before as well.

Answer 4

@Erland Sommarskog - thanks for suggestions.
I attached a log and a screenshot with the error now.51294-integritycheck.txt

Answer 5

Hi @Erland Sommarskog
I found log file and checked it for 12/26/2020.
I found this line:

2020-12-26 00:00:03.84 spid58      DBCC CHECKDB (app4_starlims_data_c2) WITH all_errormsgs, no_infomsgs, data_purity executed by VHA01\vhabhsressqlsa terminated abnormally due to error state 6. Elapsed time: 0 hours 0 minutes 1 seconds.  

Maybe this is the symptom ? I will attach a log for reference .. I have 4 databases on this server and the issue was only with one database app4_starlims_data_c2
I ran "DBCC CHECKDB (app4_starlims_data_c2) WITH all_errormsgs, no_infomsgs, data_purity" manually and it completed with no errors.
I checked that we set option 'disconnect' other users while restore database. I assume it could be the issue in my case. I unchecked this option and will monitor it. Does it make sense to you?
51374-error-log.txt