• Post Categories

  • Browse Blogs

  • Blog Stats

    • 292,711 hits
  • Syndications

    SQLServerPedia Contributor

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.

About these ads

6 Responses

  1. This was very helpful for me. I came across this issue when I restored my SQL Server 2012 MDS database. I had to enable broker explicitly after the restore and before going back in to multi user. Thanks for posting this!

  2. Hello Jose,

    Can you tell the name of tool that i can use to repair MDS in sql server 2012.

    • Mark,
      My original understanding was that there was going to be a tool to repair these types of issues but the only repair tasks available in the MDS 2012 Repair Database option of the MDS Configuration Manager include: enable CLR, enable Service Broker, and enable other necessary services.

  3. [...] Broker, either due to a timeout or notifications not being received or received incomplete.  Jose Chinchilla has [...]

  4. can u please tell me how to automate the process of hirearchy details save in mds 2012.udpSecurityMemberProcessRebuildModel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,639 other followers

%d bloggers like this: