1

I got a job DatabaseIntegrityCheck - USER_DATABASES, failing with the following error:

Date and time: 2018-10-29 02:34:13 Command: DBCC CHECKDB ([one of my databases]) WITH NO_INFOMSGS, ALL_ERRORMSGS, DATA_PURITY HResult 0x254, Level 21, State 1 Cannot continue the execution because the session is in the kill state.

I get an alert email with the following error message:

The client was unable to reuse a session with SPID 193, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

enter image description here

On the primary server I get this message:

The availability group database "all databases" is changing roles from "PRIMARY" to "RESOLVING" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.

enter image description here

and on the secondary:

The availability group database "each database" is changing roles from "SECONDARY" to "SECONDARY" because the mirroring session or availability group failed over due to role synchronization. This is an informational message only. No user action is required.

enter image description here

As I could not find out the cause of this error, I have escalated it to the sys admin friends, also because I suspect it is something to do with network.

question: as nothing about this on the error log, where else can I find any information about this matter?

I have run sp_blitz with markdown

EXEC sp_Blitz @OutputType = ‘markdown’, @CheckServerInfo = 1

and the results are here

I have also run the following command on one of my small databases test1 which is part of the availability group:

DBCC CHECKDB ([test1]) WITH ALL_ERRORMSGS, DATA_PURITY

and that gave me the results shown here and important to say, on this occasion it did not cause any failover or change of state in the availability group.

Update:

Yesterday I changed the schedule of the job DatabaseIntegrityCheck - USER_DATABASES from 2 am to 3 am and it upset the availability group at 3:43 am It seems to be caused by the dbcc checkdb

enter image description here

  • 3
    Can you post the markdown output of sp_Blitz as described here: brentozar.com/archive/2017/10/getting-help-slow-server I'm going to guess we're missing something about the server. Plus, can you try running a DBCC CHECKDB() manually on the smallest database you have? Ideally, use one that isn't user-facing, because you might actually be triggering AG failovers when this happens. – Brent Ozar Oct 29 '18 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.