We have set up sql server 2008 management dataware house (mdw) a month ago, it was working correctly for some time, our configuration is two 2-node SQL Server 2008 failover cluster (4 machine at total) as :
Node 1 : Instance A
Node 2 : Instance B + Instance C
Node 1 : Instance D
Node 2 : Stand by
so 4 instance at total. This was the configuration, our problem is:
MDW collection sets are working well for all instances except "Server Activity" collection set on instances B and C; server activity collection set is not working on this node.
When I looked at the "view log" the main error is:
The thread "ExecMasterPackage" has timed out 3600 seconds after being signaled to stop. on upload step of collection (its a cached collection)
By using dbo.sp_syscollector_update_collection_set i have set logging_level to 2 for the collection I went deeper in the error:
At performance counters part there is a warning which is repeated multiple times (not an error):
The output column "collection_time" (703) on output "Raw File Source Output" (40) and component "RAW - Read current tempoary storage - purpose - obtain counter paths" (36) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.
As far as i see the error is the job cannot be finished in one hour and error comes:
The thread "ExecMasterPackage" has timed out 3600 seconds after being signaled to stop. on upload step of collection (its a cached collection).
- All instances works with the same MDW.
- I have setup a new MDW but problem is same
- I have tried different schedules up to 6hours for uploading for Server activity, but the errors comes and job fails,
Any ideas will be appreciated,