Job 'IntegrityCheck' failed

Susja 716 Reputation points
2020-12-25T23:57:46.807+00:00

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,158 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,666 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 113.7K Reputation points MVP
    2020-12-27T10:24:47.777+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

5 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 113.7K Reputation points MVP
    2020-12-26T10:07:19.073+00:00

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

    1 person found this answer helpful.
    0 comments No comments

  2. Erland Sommarskog 113.7K Reputation points MVP
    2020-12-26T17:29:14.133+00:00

    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.

    1 person found this answer helpful.
    0 comments No comments

  3. Susja 716 Reputation points
    2020-12-26T17:16:22.077+00:00

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

    0 comments No comments

  4. Susja 716 Reputation points
    2020-12-27T00:34:22.41+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.