none
Stuck queue

    Question

  • A router died between between my MDS and SQL servers and a staging batch of 6M records got stuck. So, I've tried to clear it, but now that batch is in a "Queued to clear" state and doesn't run either. Is there any way to restart or reset the staging batch queue?
    Wednesday, March 16, 2011 4:38 PM

Answers

  • Hi

    Maybe your service broker stoppet...when I restore databases etc. the service broker has to be enabled again...

    try this code:

    USE [master]
    GO
    ALTER DATABASE [DB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    ALTER DATABASE [DB] SET  ENABLE_BROKER WITH NO_WAIT
    GO

    ALTER DATABASE [DB] SET  MULTI_USER WITH ROLLBACK IMMEDIATE
    GO

    /mm


    MM
    • Marked as answer by Dydek Thursday, March 17, 2011 2:59 PM
    Thursday, March 17, 2011 9:10 AM
  • Hi Dydek

    you can run the stored procedure :

    EXEC mdm.udpStagingSweep @userID,@versionid, @process

    userID : look in tblUser to get your userid

    versionID : look in tblModelVersion to get the versionid of your model from which you want to clear staging data

    process : set the value to 1 to start the process immediately

    Regards,

     



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    • Marked as answer by Dydek Thursday, March 17, 2011 2:59 PM
    Wednesday, March 16, 2011 7:12 PM

All replies

  • Hi Dydek

    you can run the stored procedure :

    EXEC mdm.udpStagingSweep @userID,@versionid, @process

    userID : look in tblUser to get your userid

    versionID : look in tblModelVersion to get the versionid of your model from which you want to clear staging data

    process : set the value to 1 to start the process immediately

    Regards,

     



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    • Marked as answer by Dydek Thursday, March 17, 2011 2:59 PM
    Wednesday, March 16, 2011 7:12 PM
  • Thanks, but that didn't work. The batch is still stuck and others are getting stuck behind it, too.
    Darek Danielewski BI Software, Inc.
    Thursday, March 17, 2011 4:19 AM
  • Hi Darek,

    maybe you could try to clear table stgbatch ?

    Regards,



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Thursday, March 17, 2011 6:22 AM
  • Hi

    Maybe your service broker stoppet...when I restore databases etc. the service broker has to be enabled again...

    try this code:

    USE [master]
    GO
    ALTER DATABASE [DB] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    GO

    ALTER DATABASE [DB] SET  ENABLE_BROKER WITH NO_WAIT
    GO

    ALTER DATABASE [DB] SET  MULTI_USER WITH ROLLBACK IMMEDIATE
    GO

    /mm


    MM
    • Marked as answer by Dydek Thursday, March 17, 2011 2:59 PM
    Thursday, March 17, 2011 9:10 AM
  • Hi Darek,

    I agree with MM, it is probably the service broker "staging batch" queue that is disabled.

    please check that point in SQL management Studio:

    Regards,



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Thursday, March 17, 2011 12:29 PM
  • Xavier, I don't have that queue at all. I only have a service by similar name ...

    Uploaded with ImageShack.us
    Darek Danielewski BI Software, Inc.
    Thursday, March 17, 2011 2:52 PM
  • Darek,

    please try this:

     

    USE [FusionMDS]
    GO
    /****** Object: ServiceQueue [mdm].[microsoft/mdm/queue/stagingbatch] Script Date: 03/17/2011 15:58:10 ******/
    CREATE QUEUE [mdm].[microsoft/mdm/queue/stagingbatch] WITH STATUS = ON , RETENTION = OFF , ACTIVATION
     ( STATUS = ON , PROCEDURE_NAME = [mdm].[udpStagingBatchQueueActivate] , MAX_QUEUE_READERS = 1 ,
    
     EXECUTE AS N'mds_ssb_user' ), POISON_MESSAGE_HANDLING (STATUS = ON) 

     

    and after that :

     

    USE [FusionMDS]
    GO /****** Object: BrokerService [microsoft/mdm/service/stagingbatch] Script Date: 03/17/2011 15:59:59 ******/ ALTER SERVICE [microsoft/mdm/service/stagingbatch] ON QUEUE [mdm].[microsoft/mdm/queue/stagingbatch]
    Regards,

     



    Xavier Averbouch
    Avanade , FRANCE
    If a post answers your question, please click "Mark As Answer" on that post and "Vote as Helpful".
    Thursday, March 17, 2011 2:59 PM
  • After executing Biz_mm code followed by Xavier's first advice, I have all my queues populated and operational again. Thanks guys! Your help is much appreciated!
    Darek Danielewski BI Software, Inc.
    Thursday, March 17, 2011 3:00 PM