October 9, 2012 at 8:03 am
I am running SQL Server 2008 R2 in a clustered environment. We have had Data Collector configured and sending information to our central MDW database for some time now without issue. There was a requirement to configure database mirroring for one particular database. Once the mirror was created, the job called Collect_Set_3_Upload continuously fails with the following error:
Hresult: 0x80040E14 Description: "The database "JobsUS" cannot be opened. It is acting as a mirror database."
I found only one link describing the issue. http://social.msdn.microsoft.com/Forums/en-CA/sqldatabaseengine/thread/fd93449f-7834-493e-8529-52e9af2c129a However, there's no real solution to this specific problem. I understand that the job is not mirror aware, but how do I go about resolving this? Can I modify the code that runs the collection upload step?
Additionally, only collection set 3 upload is failing, not 2.
Any help is appreciated.
May 23, 2013 at 2:53 pm
I am seeing this exact same issue on a SQL 2012 instance (SP1CU3). The interesting thing is that I have multiple instances with this same scenario, but I only get the error on one of them. It is the same collector that I am having issues with, which is the Query Statistics collector. Did you ever solve this problem? Since I only see this issue on one of my instances and since nobody else has replied to this post, I am assuming this only happens in certain situations, but I have yet to figure it out. I definitely have other instances where there are mirrored databases currently in restoring mode and I do not have any problems with this collector. Anybody have any ideas on this issue?
May 24, 2013 at 12:48 pm
In my situation, stopping the data collector, clearing all the cache files, then restarting collection did not work. I was still receiving the error that one of the databases could not be opened. I tracked this down to the fact that this data collector queries the dmv sys.dm_exec_query_stats. Since this dmv contained cached plans for a database that was now mirrored (in recovery), the data collector was attempting to connect to that database, which of course failed. I then stopped data collection, deleted the cache files, then cleared the plan cache (dbcc freeproccache). When I started collection back up after that the error was gone. I did this after hours and only one of the ten databases was actually primary on this instance, so I was ok with clearing the plan cache, but I could see situations where clearing the plan cache would not be desirable. It may be possible to clear the plan cache for one specific database, but I have not looked into that yet. I thought I would share in case anyone else runs into the same issue.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply