I have a two node SQL 2012 AlwaysOn HADR cluster (v11.0.3412) with 3 availability groups configured. The AG groups are set to synchronous mode and the secondary is not readable (we do not want the synchronous replica readable so we do not risk any reads causing contention so we maintain fast performance).
On the secondary we are getting a persistent failure with the Data Collector job called Collection_Set_3_Upload. The failure occurs within the second job step. That job step is executing the following command:
dcexec -u -s 3 -i "$(ESCAPE_DQUOTE(MACH))\$(ESCAPE_DQUOTE(INST))"
The error message is as follows:
Log Job History (collection_set_3_upload) Step ID 2 Server CLUSTERNODE2 Job Name collection_set_3_upload Step Name collection_set_3_upload_upload Duration 00:00:07 Sql Severity 0 Sql Message ID 0 Operator Emailed
Operator Net sent Operator Paged
Retries Attempted 2
Message Executed as user: MYDOMAIN\MYSERVICEACCOUNT. SSIS error. Component name: DFT - Find and > save plan text, Code: -1071636471, Subcomponent: LKUP - Look up query plans on target server , Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80040E14 Description: "The target database, 'MyDatabase', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group. For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.". . SSIS error. Component name: DFT - Find and save plan text, Code: -1071611309, Subcomponent: LKUP - Look up query plans on target server , Description: OLE DB error occurred while fetching parameterized rowset. Check SQLCommand and SqlCommandParam properties. . SSIS error. Component name: DFT - Find and save plan text, Code: -1073450974, Subcomponent: SSIS.Pipeline, Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "LKUP - Look up query plans on target server" (2) failed with error code 0xC0208253 while processing input "Lookup Input" (16). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. .The master package exited with error, previous error messages should explain the cause. Process Exit Code 5. . NOTE: The step was retried the requested number of times (2) without succeeding. The step failed.
I know I can prevent this error message by enabling readable secondaries, but we do not want this. Is there any other solution to fix this problem? I cannot find any leads via Google.
I have tried stopping the data collection jobs and purging the cache directory but to no avail. It will succeed the first time then persistently fail again with the same message every time after that.
Thank you in advance.
edit - The problem magically stopped after it was happening for about 2 weeks. I don't know what fixed it but I had switched it to readable secondaries and failed it over. Since the last time I switched it to non-readable secondaries again it hasn't failed since. - closing the question.