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.
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.
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.
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.
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