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 [2], 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 [2], 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.

  • If your secondary database in the AG is not readable, then you are out of luck. The only way is to make your secondary atleast readable. Or other way is to add a step to the job to first make secondary readable, let the job run and then revert the secondary back. – Kin Shah Jun 4 '14 at 19:11
  • I would agree with you but this only affects a single AG. We have 4 AG's total on this environment, all of which are set to not allow reads. Once I make that one AG that has been failing readable on the secondary, the job succeeds. I would expect that it would fail because the others are not readable. This is still true even after I fail over and fail back. – Alf47 Jun 4 '14 at 20:24
  • I don't know what happened but the problem mysteriously disappeared. The AGs are all set to not allow reads. – Alf47 Jun 23 '14 at 18:36

This is what I ended up implementing in our AlwaysOn Cluster with DataCollection. On then secondaries the job "collection_set_3_upload" would fail.

You'll have to apply, or at least check for, the modifications of the query after every installation of SQL Server CUs.

Root Cause explanation from the following page:

When the collector finds an “interesting” query from sys.dm_exec_query_stats, it calls the stored procedure msdb.dbo.sp_syscollector_text_query_plan_lookup to get the query plan from cache using sys.dm_exec_text_query_plan. That function will error out if the plan references a database that has the role mirror or secondary.


| improve this answer | |
  • 1
    What exactly did you "end up implementing"? If the linked page contains the answer, you may want to add some salient bits to your answer in case the blog page dies in future. – Max Vernon Jul 3 '18 at 18:20

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.