Master Data Services Error: Resolving from a long-running stucked Staging Batch with Status “Queued to Clear” or “Not Running”

In some ocassions,  you may run into a runaway, long-running or stucked staging batch with Master Data Services in SQL Server 2008 R2. The issue happens most frequently when a fairly large batch of rows are being processed for update or deletion.

The issue has to do with Service Broker, either due to a timeout or notifications not being received or received incomplete.  You can confirm the issue when you query the queue [mdm].[microsoft/mdm/queue/stagingbatch]. You will see one or more messages in this queue. After searching through a couple of forum threads I found the solution to be fairly simple: rollback transactions an re-enable Service Broker. The steps are as follows:

1. Set the MDS database in single user mode and rollback uncommitted transactions.

   ALTER DATABASE [MDS] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;

2. Re-enable Service Broker on the MDS database.

   ALTER DATABASE [MDS] SET  ENABLE_BROKER WITH NO_WAIT;

3. Set the MDS database back in to “regular” multi-user mode.

   ALTER DATABASE [MDS] SET  MULTI_USER WITH ROLLBACK IMMEDIATE;

Once the MDS database is brought back online, all queued messages should be cleared out and MDS database will be able to communicate normally with Service Broker.

You can check Books on Line for more information on the difference between “NO WAIT” and “WITH ROLLBACK IMMEDIATE” options when issuing the ALTER DATABASE command here. Pinal Dave has a good post about the difference between these two options as well here.

Hope this post has helped you. Your feedback is greatly appreciated.

Advertisement

SQL Server 2012 Master Data Services Error: The required .svc handler mappings are not installed in IIS.

Microsoft SQL Server 2012 Release Candidate 0 (RC 0) has a confirmed issue with Master Data Services configuration. The following error is raised by the Master Data Services Configuration Manager when launched:

Before creating an MDS web application, you must correct the following errors:
Internet Information Services (IIS) is not configured on this server.
The required .svc handler mappings are not installed in IIS. For more information, see http://go.microsoft.com/fwlink/?LinkId=226284.
For web application requirements, see http://go.microsoft.com/fwlink/?LinkId=215355.

This issue has been communicated to Microsoft via Connect ID: 701993 and is documented in the Technet Article “Troubleshoot Installation and Configuration Issues (Master Data Services in SQL Server 2012)”

The Workaround

After several days trying to figure out the issue, today I received notice from Reagan Templin (LinkedIn | Twitter), technical writer for Microsoft, that the MDS Team has identified the issue. This issue will NOT be fixed in the current RCO but will be fixed in the RTM version. The error can be replicated under the following conditions:

1. Configuring Master Data Services on a server that is not joined to a Windows domain
2. Running Master Data Service Configuration Manager with a local account (non-domain user) on a server joined to a Windows domain.

The workaround is simply to join the server to a Windows domain and launch Master Data Services Configuration Manager with a domain account that has administrator privileges. In my case,  after I joined the server to a domain I was able to complete by MDS configuration.

Many thanks to Reagan Templin for her above and beyond customer service. She followed up on this issue directly with the MDS Team after I posted a message on Twitter about the issue I was having and kept me posted with updates towards resolving the issue.