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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy