Category Archives: Service Broker

Count active Thread for Service Broker Queue in SQL Server

Count active Thread for Service Broker Queue in SQL Server

Since we have big service broker setup, it is very important to know for us about thread running for target queue. Target queue is the queue which suppose to do the work of actual business logic and we used to give proper naming convention to each queue. Suppose we have one billing queue then we suppose to name it as “BillingInitiatorQueue”, “BillingTargetQueue” etc.

Since target queue has responsibility to implement actual business logic, I am always interested to see how many thread currently running for each target queue. Suppose I have set 15 thread for “BillingTargetQueue” but it may happen that I have only 5 to 10 messages in queue and hence all 15 thread will not be active. If I don’t have any message in queue then obviously no thread will be active though I set 15. Sometime I may have 1000 message to process in target queue but even I have 10 thread active only. Remaining 5 thread may not be getting proper system resources or anything else. I can’t even decide whether to increase thread or decrease without knowing actual number of message, time thread is taking to process. I have already written articles about “Find Service Broker Queue Count in SQL Server (Click Here) ” and “Keep watch on Service Broker Queue Delay in SQL Server (Click Here) ”. Today I am going to show the script which will give the count of thread for target queue running at the moment.


SELECT

OBJECT_NAME(queue_id) AS QueueName,

COUNT(*) AS ThreadCount

FROM

sys.dm_broker_activated_tasks WITH (NOLOCK)

--Comment the WHERE clause to see thread count for all queue

--not only target queue

WHERE (OBJECT_NAME(queue_id) LIKE '%Target%')

GROUP BY

OBJECT_NAME(queue_id)

HAVING

(COUNT(*) > 0)

ORDER BY

ThreadCount DESC

GO

I have written few more article about service broker, have a look if you are interested.

Service Broker Articles:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)
  • Keep watch on Service Broker Queue Delay in SQL Server (Click Here)
  • Deal with message queue conversation with state CD  in Service Broker (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Deal with message queue conversation with state CD in Service Broker

Deal with message queue conversation with state CD in Service Broker

Each side of a Service Broker conversation is represented by a conversation endpoint and sys.conversation_endpoints catalog view contains a row per conversation endpoint in the database. Generally when service broker accept record in message queue, it used to process message and remove it after closing by its own.

Recently at one of client site I have seen that messages are having “CD” state in sys.conversation_endpoints catalog view but it is not being removed from the message queue. Message queue was having approx half a million message with “CD” closed state so in order to remove those messages, I have created one small script to clean it up which might be helpful for you as well sometime.

CREATE PROCEDURE [CleanConversations]
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY

DECLARE @handle UNIQUEIDENTIFIER

DECLARE conv CURSOR FOR SELECT a.conversation_handle FROM sys.conversation_endpoints a with(nolock)
WHERE state = 'CD'

OPEN conv

FETCH NEXT FROM conv INTO @handle

WHILE @@FETCH_STATUS = 0 BEGIN

END CONVERSATION @handle WITH CLEANUP
FETCH NEXT FROM conv INTO @handle
PRINT @handle

END

CLOSE conv

DEALLOCATE conv

END TRY

BEGIN CATCH

DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
RAISERROR('Error in %s: %s', 16, 1, 'CleanConversations', @msg)

END CATCH
END
GO

After having the above stored procedure, you can run it and clear all conversation which are already closed. Generally message should be closed and removed by its own but once in a blue moon if you come across the situation which I have observed at my client site, you can use this SP.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Keep watch on Service Broker Queue Delay in SQL Server

Keep watch on Service Broker Queue Delay in SQL Server

We have very large setup of Service Broker enabled in our live databases. Out of which, some of the nightly business processes create a huge target queue which we suppose to finish during the night time itself. If we can’t cleanup all these queue in night time, it creates additional overhead during the day time when most of the customer uses our SaaS product which we can’t afford.

This is the reason I am using one very small script which gives me approximate (not exact) idea about how much time current queue will take to complete. If it is taking longer time then I can increase the threads to process it faster to get it done in night time. BTW, one needs to keep watch on locking if threads are blocking each other, you won’t get much benefit of increasing threads.

Note: I am using one user defined function “GetQueueCount” in the script below so if you don’t have function “GetQueueCount” than kindly create it first.

Here is the script I am using:

/*
EXECUTE CheckQueueDelay 'ProviceYourQueueName',100
*/
CREATE PROCEDURE CheckQueueDelay
(
@QueueName VARCHAR(100),
@MaxMin INT = 10
)
AS
DECLARE @cnt INT
SELECT @cnt = 1

DECLARE @a INT, @b INT, @tot INT

WHILE @cnt <= @MaxMin BEGIN
SELECT @a = rows FROM GetQueueCount() WHERE name = @QueueName
IF @a = 0 break;
WAITFOR DELAY '00:01:00'
SELECT @b = rows FROM GetQueueCount() WHERE name = @QueueName
IF @b = 0 break;
SELECT @tot = @a - @b
RAISERROR ('Processed %d rows in 1 minute', 10, 1, @tot) WITH NOWAIT
SELECT @tot = @b / (@a - @b)
RAISERROR ('Need %d minutes to process remaining %d rows', 10, 1, @tot, @b) WITH NOWAIT
SELECT @cnt = @cnt + 1
END
GO

Now, you can execute this stored procedure to see how much time would it take to complete the queue. If queue is processing and it is also getting new records at the same time, you might not have exact value.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Setup service broker between two databases in SQL Server.

Setup service broker between two databases in SQL Server.

My earlier article “Service Broker implementation between two tables in one database”  explained how to setup service broker between two table in one database. Today I am explaining how to setup service broker between two tables resides in two different databases in one SQL Server instance.

I will be using the same table, queues, contracts, services and message types given in “Service Broker implementation between two tables in one database” article so if you have not read this article, I would recommend to take a look there once.

Creating two different databases in one SQL Server 2012 instance.

USE MASTER;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBMain')
DROP DATABASE ExtremeAdviceSBMain;
GO

CREATE DATABASE ExtremeAdviceSBMain;
GO

IF EXISTS (SELECT 1 FROM sys.databases WHERE name = N'ExtremeAdviceSBReceiver')
DROP DATABASE ExtremeAdviceSBReceiver;
GO

CREATE DATABASE ExtremeAdviceSBReceiver;
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBMain  SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBMain  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBMain TO [sa];
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBReceiver SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBReceiver  SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBReceiver TO [sa];
GO

Let us now create basic architecture of service broker like message type, contract, queue and services in both databases ExtremeAdviceSBMain & ExtremeAdviceSBReceiver (target and initiator). Please note that I have used the same service broker object I have used in earlier article. We may eliminate few things but I will cover it up later on.

USE ExtremeAdviceSBReceiver
GO

--Create Message Type in target database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in target database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO

--Create Target Queue
CREATE QUEUE OrderTargetQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Target]  ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order])
GO

USE ExtremeAdviceSBMain
GO

--Create Message Type in initiator database
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract in initiator database
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO
--Create initiator Queue

CREATE QUEUE OrderInitiatorQueue
WITH STATUS = ON
GO

CREATE SERVICE [EXTREME://EX/Service/Order/Initiator]  ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order])
GO

Now we will create two tables. One table in initiator database which is OrderDetail. Sales person will generate sales order and data will falls under that table. If sales person inserting duplicate order, it will caught by trigger and will be inserted in OrderHistory table. For more detail about this concept, have a look at my earlier article.

USE ExtremeAdviceSBMain
GO

--two sample table for demonstration
CREATE TABLE OrderDetail
(
OrderID INT IDENTITY(1,1),
ClientID VARCHAR(10),
ProjectID VARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE()
)
GO

USE ExtremeAdviceSBReceiver
GO

CREATE TABLE OrderHistory
(
ID INT IDENTITY(1,1),
OrderID INT,
DuplicateWith INT,
CheckDate DATETIME DEFAULT GETDATE()
)
GO

Now we will create main logic which will send and process original message along with trigger on OrderDetail table which will activate service broker as soon as new record inserted in OrderDetail table.

USE ExtremeAdviceSBMain
GO
--Create this SP in Initiator database
--Create SP which will be called from app
--or trigger or by any other way
--and activate service broker
CREATE PROCEDURE [SendOrderRequestMessage]
(
@data AS XML
)
AS
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE [EXTREME://EX/Service/Order/Initiator]
TO SERVICE N'EXTREME://EX/Service/Order/Target'
ON CONTRACT [EXTREME://EX/Contract/Order]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data);
--END CONVERSATION @DialogHandle
COMMIT ;

END TRY

BEGIN CATCH
ROLLBACK TRAN;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ERROR_MESSAGE();
PRINT @msg
END CATCH;
GO

USE ExtremeAdviceSBReceiver
GO

--Create this SP in target database
-- Create actual business logic SP
CREATE PROCEDURE [CheckOrderMessage]
(
@data XML
)
AS
BEGIN TRY

DECLARE @MessageType VARCHAR(50)
,@OrderID INT
,@ClientID VARCHAR(10)
,@ProjectID VARCHAR(50)

SELECT  @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'),
@OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'),
@ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'),
@ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)')

SELECT @MessageType = ISNULL(@MessageType,'')

IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN
DECLARE @OrderIDOld INT
,@ClientIDOld VARCHAR(10)
,@ProjectIDOld VARCHAR(50)

SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID
FROM   ExtremeAdviceSBMain.dbo.OrderDetail
WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID

IF(@OrderIDOld IS NOT NULL)
BEGIN
INSERT INTO OrderHistory (OrderID,DuplicateWith)
SELECT @OrderIDOld,@OrderID
END
RETURN
END
END TRY

BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
END CATCH
RETURN
GO

--Create Target queue reader SP
CREATE PROC [OrderTargetQueueReader]
AS
SET NOCOUNT ON;

DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderTargetQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0)
BEGIN
BREAK
END
SET @XML = CAST(@MessageBody AS XML);
IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request'
BEGIN
--PRINT 'executing CheckOrderMessage @XML'

EXECUTE CheckOrderMessage @XML

--PRINT 'sending response'

SELECT @xml = 'OK';

SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml);
--PRINT 'ending cnversation'
END CONVERSATION @ConversationHandle;
END
END
END TRY
BEGIN CATCH

DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()

SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
SELECT @msg
END CATCH
GO

USE ExtremeAdviceSBMain
GO

--create following SP to handle response of target in initiatordatabase
--Create Initiator queue reader SP
CREATE PROC [OrderInitiatorQueueReader]
AS
SET NOCOUNT ON;
DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderInitiatorQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @ConversationHandle;
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
END CATCH
GO

-------service broker call from trigger.
CREATE TRIGGER trg_OrderDetail_Ins
ON  OrderDetail
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Data XML
SELECT @Data =
(
SELECT
'OrderHistory' AS 'Header/@MessageType',
OrderID AS 'Header/@OrderID',
ClientID AS 'Header/@ClientID',
ProjectID AS 'Header/@ProjectID'
FROM
(SELECT * FROM inserted) AS T
FOR XML PATH(''), ROOT('OrderMessage'), TYPE
)

EXEC [SendOrderRequestMessage] @Data
END
GO

Once you are ready with this structure, you are all set to go. Let us insert one record in OrderDetail table.

USE ExtremeAdviceSBMain
GO

--Testing SB by inserting one record in OrderDetail Table
--which will execute Trigger and trigger will activate SB
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record which we have inserted above.
SELECT * FROM OrderDetail
GO

--you will get no record in OrderHistory table
--as we have not inserted duplicate record yet.
SELECT * FROM  ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

Since we have not inserted any duplicate records yet, we won’t get any record in OrderHistory table. Let us inserting duplicate record in OrderDetail table.

--inserting same record again so SB will detect it as duplicate order
--that will be inserted in ORderHistory table
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record in OrderHistory table
--as we have inserted duplicate record
SELECT * FROM ExtremeAdviceSBReceiver.dbo.OrderHistory
GO

We, now, suppose to get record in OrderHistory table but unfortunately we won’t get it. There are two possibilities that either our initiator queue doesn’t have sent message to target or target have received the record but it has not processed it. Right???? Let us check it.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see that our “OrderTargetQueue” has record to process. Then question is why it has not processed?

Well, we have had Trigger on OrderDetail table which has activated our initiator queue and hence record reached to target queue but target queue has not been activated and hence it has not processed target message.

We can process message with following SP either manually or by sql server job as we have not activate it automatically.

 USE ExtremeAdviceSBReceiver
 GO

EXEC [OrderTargetQueueReader]
GO

Now let us see whether record is processed or not.

SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBMain.DBO.OrderInitiatorQueue
SELECT CAST(MESSAGE_BODY AS XML), * FROM ExtremeAdviceSBReceiver.DBO.OrderTargetQueue
GO

You can see “OrderTargetQueue”has not record now as our message is processed and you can also see the entry in “OrderHistory” table also as we had one duplicate record.

Still “OrderInitiatorQueue” has record to process. You can see that it is just a blank message for end dialog with “OK” status which has sent back by target queue after processing messages.

We can run following SP to process end dialog in initiator queue.

USE ExtremeAdviceSBMain
GO

EXEC [OrderInitiatorQueueReader]
GO

I will shortly come up with article which will run full service broker cycle by its own without manual interaction.

If you wish to refer my other Service Broker related article, have a look at following list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)
  • Service Broker Queue Activation and Thread in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Service Broker Queue Activation and Thread in SQL Server

Service Broker Queue Activation and Thread in SQL Server

I have recently written one article “Service Broker implementation between two tables in one database” where I have created two queues “Target Queue” and “Initiator Queue”.  Following is the code of one of the queue.

 CREATE QUEUE OrderTargetQueue
 WITH STATUS = ON,
 ACTIVATION(
 STATUS=ON,
 PROCEDURE_NAME = OrderTargetQueueReader,
 --You can change queue reader number based on your queue load
 MAX_QUEUE_READERS = 1,
 EXECUTE AS OWNER
 );
 GO
 

After reading and successfully implementing that example, one of the readers had difficulty to understand the script I have given for the queue so thought to give some insight about important part of this script.

Second line of this script is “WITH STATUS = ON” right below the “CREATE QUEUE” line indicates that the queue we are creating is enabled. Use “OFF” status either in “CREATE QUEUE” or in “ALTER QUEUE” to disable the queue. If you disable the queue, queue will stop accepting messages.

If queue is enable and active, it supposes to have some property setup to define its behavior.  Third line of the code “ACTIVATION (” defines those property.

Fourth line “STATUS=ON,” right below “ACTIVATION (” indicates that Queue is active and hence it processes the message it receives.

Fifth line “PROCEDURE_NAME=OrderTargetQueueReader,” defines the stored procedure which reads the message of target queue (in our case) and process it.

Sixth and most important is “MAX_QUEUE_READERS=1,” indicates that only one execution of “OrderTargetQueueReader” stored procedure keeps running, no matter whether there is one message or more than one. Sometime it happens that we have hundreds or thousands of messages to process and we need multiple execution of stored procedure as parallel execution to process message faster and clear the queue.  You have to ALTER the queue and increase the thread of QUEUE based on your requirement, later on you can decrease it back.

We can understand this by one example. Suppose we go to super market to buy monthly grocery stuff. If there are very few customer, mall manager keeps one payment window open. Whenever customer increases, mall manager decides to open multiple payment windows to process payment faster so that customer doesn’t need to wait for long time in queue.

Though we had only one queue in mall so all customer stands in one row only, as soon as multiple windows get opens, customer moves to another queue to finish their payment early and this reshuffle of queue has some overhead. This same concept applies the Service Broker too and Microsoft has handled this issue very efficiently, I will come up with detail knowledge of this concept very soon.

BTW, let me show you how to ALTER the QUEUE to increase the thread.

 ALTER QUEUE [OrderTargetQueue]
 WITH STATUS = ON ,
 ACTIVATION
 (
 STATUS = ON ,
 PROCEDURE_NAME = [dbo].[OrderTargetQueueReader] ,
 MAX_QUEUE_READERS = 5 ,
 EXECUTE AS OWNER  )
 GO
 

Till then stay tuned!!!!!!!!!!

If you wish to refer my other Service Broker related article, have a look at following list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)
  • Service Broker implementation between two tables in one database (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Service Broker implementation between two tables in one database

Service Broker implementation between two tables in one database

SBDemoI have already provided small demonstration of how service broker works at “Small demonstration of Service broker for beginners” along with list of articles related to service broker from the concept of service broker to its implementation and definition of important part of Service Broker so I will not those concept here in this article, If you wish, you can have a look at those articles, here is the list:

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)
  • Deal with size of Transmission Queue table sysxmitqueue in MSDB database (Click Here)

Let us now start implementing Service Broker for two tables in one database.

Let me explain the business logic which will be used in this article.

Sales people in Company “Extreme Trading” (ET) goes to client site and accept the order from client. Sales people may use iPad, iPhone or any other smart phone, tablet, laptop or other portable device to insert order of client.

There is a rule in company that any order shouldn’t have same client id and project id. If same client give order for same project, it should be added in original order. Original order should be edited. It shouldn’t insert new order id for same client, project combination.

Well, we can define composite primary key or put some validation or check constraint etc. but I don’t want to debate on this rule.

If any duplicate order comes, it should be accepted from the sales people but later on it should insert original order id along with new order id in one table so back office team can handle it and sales person could be free immediately without even waiting for validation gets completed.

Demonstrating this exercise, we will need one database along with two sample table before we start implementing actual service broker.

--create database.
CREATE DATABASE ExtremeAdviceSBDemo
GO

--making broker enable for database.
ALTER DATABASE ExtremeAdviceSBDemo SET ENABLE_BROKER
ALTER DATABASE ExtremeAdviceSBDemo SET TRUSTWORTHY ON;
ALTER AUTHORIZATION ON DATABASE::ExtremeAdviceSBDemo TO [sa];
GO

USE ExtremeAdviceSBDemo
GO

--two sample table for demonstration
CREATE TABLE OrderDetail
(
OrderID INT IDENTITY(1,1),
ClientID VARCHAR(10),
ProjectID VARCHAR(50),
OrderDate DATETIME DEFAULT GETDATE()
)
GO

CREATE TABLE OrderHistory
(
ID INT IDENTITY(1,1),
OrderID INT,
DuplicateWith INT,
CheckDate DATETIME DEFAULT GETDATE()
)
GO

Now, let us start service broker code. I will not explain each block of code here, if you wish, you can refer my earlier articles given in first paragraph here.

--Create Message Type
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Request] VALIDATION = WELL_FORMED_XML
CREATE MESSAGE TYPE [EXTREME://EX/Message/Order/Response] VALIDATION = WELL_FORMED_XML
GO

--Create Contract
CREATE CONTRACT [EXTREME://EX/Contract/Order]
(
[EXTREME://EX/Message/Order/Request] SENT BY INITIATOR,
[EXTREME://EX/Message/Order/Response] SENT BY TARGET
);
GO

--Create Initiator queue reader SP
CREATE PROC [OrderInitiatorQueueReader]
AS
SET NOCOUNT ON;
DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY

WHILE (1 = 1)
BEGIN
BEGIN TRANSACTION
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderInitiatorQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0 AND @@TRANCOUNT>0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END

IF @MessageTypeName = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @ConversationHandle;
END
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg

END CATCH
GO

--Create Initiator Queue
CREATE QUEUE OrderInitiatorQueue
WITH STATUS = ON,
ACTIVATION(
PROCEDURE_NAME = OrderInitiatorQueueReader,
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
);
GO

-- Create actual business logic SP
CREATE PROCEDURE [CheckOrderMessage]
(
@data XML
)
AS
BEGIN TRY

DECLARE @MessageType VARCHAR(50)
,@OrderID INT
,@ClientID VARCHAR(10)
,@ProjectID VARCHAR(50)

SELECT  @MessageType = @Data.value('(OrderMessage/Header/@MessageType)[1]','VARCHAR(50)'),
@OrderID = @data.value('(OrderMessage/Header/@OrderID)[1]','INT'),
@ClientID = @Data.value('(OrderMessage/Header/@ClientID)[1]','VARCHAR(10)'),
@ProjectID = @data.value('(OrderMessage/Header/@ProjectID)[1]','VARCHAR(50)')

SELECT @MessageType = ISNULL(@MessageType,'')

IF @MessageType = 'OrderHistory' AND @OrderID IS NOT NULL BEGIN
DECLARE @OrderIDOld INT
,@ClientIDOld VARCHAR(10)
,@ProjectIDOld VARCHAR(50)

SELECT @ClientIDOld=ClientID, @ProjectIDOld=ProjectID, @OrderIDOld=OrderID
FROM OrderDetail
WHERE ClientID=@ClientID AND ProjectID=@ProjectID AND OrderID<>@OrderID

IF(@OrderIDOld IS NOT NULL)
BEGIN
INSERT INTO OrderHistory (OrderID,DuplicateWith)
SELECT @OrderIDOld,@OrderID
END
RETURN
END
END TRY

BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
END CATCH
RETURN
GO

--Create Target queue reader SP
CREATE PROC [OrderTargetQueueReader]
AS
SET NOCOUNT ON;

DECLARE              @XML XML,
@MessageBody VARBINARY(MAX),
@MessageTypeName SYSNAME,
@ConversationHandle UNIQUEIDENTIFIER,
@Response XML

BEGIN TRY
WHILE (1 = 1)
BEGIN
WAITFOR (
RECEIVE TOP(1)
@MessageTypeName = message_type_name,
@MessageBody = message_body,
@ConversationHandle = [conversation_handle]
FROM dbo.OrderTargetQueue
), TIMEOUT 60000

IF (@@ROWCOUNT = 0)
BEGIN
BREAK
END
SET @XML = CAST(@MessageBody AS XML);
IF @MessageTypeName = 'EXTREME://EX/Message/Order/Request'
BEGIN
--PRINT 'executing CheckOrderMessage @XML'
EXECUTE CheckOrderMessage @XML

--PRINT 'sending response'
SELECT @xml = 'OK';
SEND ON CONVERSATION @ConversationHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Response](@xml);
--PRINT 'ending cnversation'
END CONVERSATION @ConversationHandle;
END
END
END TRY
BEGIN CATCH
DECLARE @msg VARCHAR(MAX)
SELECT @msg = ERROR_MESSAGE()
DECLARE @line INT, @proc VARCHAR(MAX)
SELECT @line = ERROR_LINE(), @proc = ERROR_PROCEDURE()
SELECT @msg = 'proc: ' + @proc + '; line: ' + CAST(@line as varchar) + '; msg: ' + @msg
SELECT @msg
END CATCH
GO

--Create Target Queue
CREATE QUEUE OrderTargetQueue
WITH STATUS = ON,
ACTIVATION(
PROCEDURE_NAME = OrderTargetQueueReader,
--You can change queue reader number based on your queue load
MAX_QUEUE_READERS = 1,
EXECUTE AS OWNER
);
GO

--------------Create SP which will be called from app
--or trigger or by any other way
--and activate service broker
CREATE PROCEDURE [SendOrderRequestMessage]
(
@data AS XML
)
AS
SET XACT_ABORT OFF;
BEGIN TRY
BEGIN TRANSACTION

DECLARE @DialogHandle UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @DialogHandle
FROM SERVICE [EXTREME://EX/Service/Order/Initiator]
TO SERVICE N'EXTREME://EX/Service/Order/Target'
ON CONTRACT [EXTREME://EX/Contract/Order]
WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @DialogHandle
MESSAGE TYPE [EXTREME://EX/Message/Order/Request](@data);
--END CONVERSATION @DialogHandle
COMMIT ;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
DECLARE @msg VARCHAR(MAX);
SELECT @msg = ERROR_MESSAGE();
PRINT @msg
END CATCH;
GO

--Create Initiator Service
CREATE SERVICE [EXTREME://EX/Service/Order/Initiator]  ON QUEUE [OrderInitiatorQueue] ([EXTREME://EX/Contract/Order])
GO

--Create Target Service
CREATE SERVICE [EXTREME://EX/Service/Order/Target]  ON QUEUE [OrderTargetQueue] ([EXTREME://EX/Contract/Order])
GO

Now, we will define one trigger which will call SB to check whether order is duplicate or not every time we insert new record (not calling trigger for Update, you can do it if it is your business need)

-------service broker call from trigger.
CREATE TRIGGER trg_OrderDetail_Ins
ON  OrderDetail
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;

DECLARE @Data XML
SELECT @Data =
(
SELECT
'OrderHistory' AS 'Header/@MessageType',
OrderID AS 'Header/@OrderID',
ClientID AS 'Header/@ClientID',
ProjectID AS 'Header/@ProjectID'
FROM
(SELECT * FROM inserted) AS T
FOR XML PATH(''), ROOT('OrderMessage'), TYPE
)

EXEC [SendOrderRequestMessage] @Data
END
GO

Now, we are ready with service broker architecture and ready to test whether SB is working or not.

--Testing SB by inserting one record in OrderDetail Table
--which will execute Trigger and trigger will activate SB
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record which we have inserted above.
SELECT * FROM OrderDetail
GO

--you will get no record in OrderHistory table
--as we have not inserted duplicate record yet.
SELECT * FROM OrderHistory
GO

--inserting same record again so SB will detect it as duplicate order
--that will be inserted in ORderHistory table
INSERT INTO OrderDetail(ClientID,ProjectID)
SELECT 'TC01','NY Healthcare'
GO

--you will get one record in OrderHistory table
--as we have inserted duplicate record
SELECT * FROM OrderHistory
GO

I have not handled Error properly in CATCH block of Stored Procedure but just printed error message on screen because error handling is out of scope of this article, If you want to handle Error properly in Stored Procedure, have a look at my earlier article here.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Deal with size of Transmission Queue table sysxmitqueue in MSDB database

Deal with size of Transmission Queue table sysxmitqueue in MSDB database

I have explained earlier that how to purge transmission queue of service broker, Today I come up with one more solution related to transmission queue. I have received one question sometime back on Facebook page of my blog.

Reader has asked me that his MSDB database size is huge (87 GB) and even after shrinking, he wasn’t able to decrease the size.

I have sent him one query (find that query here) to execute in his MSDB database which returns the information about the size of table so he did that and I came to know “sysxmitqueue” table in MSDB database is consuming approx 85GB space.

Sysxmitqueue system table is used for transmission queue. As I have explained in my earlier article at “how to purge transmission queue of service broker”, close the open conversation with that script or execute following command in database.

ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;
GO

Please note that, the solution I am giving here is based on my personal experience, before executing this command in your live production environment, think twice whether this is feasible for your environment or not.

BTW, I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)
  • Purge target Transmission queue of Service Broker in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Purge target Transmission queue of Service Broker in SQL Server

Purge target Transmission queue of Service Broker in SQL Server

Last Sunday I have got very interesting question from one of my blog reader and before we discuss his question and solve it, let me give you some basic understanding regarding how message traverse between Initiator and Target Queue in very simple word by eliminating deep technicality.

When any message comes to service broker, message has to pass through various way including validation until it reaches to target queue and get processed.

Transmission Queue plays an important role in whole service broker architecture. Transmission Queue works as a “postman” between Initiator Queue and Target Queue.

Let me give you this understanding in simple words, without including deep technical explanation.

Suppose we have two server “S1” and “S2”. We have service broker implemented between these two servers. S1 is an initiator and S2 is a target. S1 sends message to process to S2 and S2 replies back to S1 via Transmission Queue.

Here is simple explanation in brief wording.

  • S1 send message to process to S2 via Transmission Queue and starts conversation dialog.
  • S2 receive message from S1’s Transmission Queue and acknowledge the same to S1 via S2’s Transmission Queue
  • Now, S2 process the message and again acknowledge the same to S1 via S2’s Transmission Queue
  • Finally message get processed and S2 send acknowledge of the same via Transmission Queue to S1 to finally close the conversation dialog
  • When S1 closes the conversation, it acknowledge the same to S2 and message gets deleted from the transmission queue of S2.

Please note that, our task get completes in first three steps whereas fourth & fifth steps are formality to close open conversation in S1 & S2.

I have written articles to watching the message count currently stays inside different queues.

  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)

One of the blog reader has implemented the functions I have given in both of the above articles and found that his initiator and target queue always having few hundreds message in peak business hours and become even zero many times but his transaction queue in target server (S2 in our example) having millions of message unprocessed. He wanted me to help him in dealing with that huge queue.

Obviously I don’t have access to his server so I have to guess after getting certain information from him. I have asked him to execute following query in his S2 server ‘s database where he has setup service broker and transmission queue showing millions of records.

SELECT
'MessageNeedsToClose' AS ActionToTake,
COUNT(*) AS RowsCount
FROM
sys.transmission_queue
WHERE
message_body IS NULL
AND is_end_of_dialog = 1
AND enqueue_time < getdate() - 7
UNION ALL
SELECT
'MessageToKeep' AS ActionToTake,
COUNT(*) AS RowsCount
FROM
sys.transmission_queue
WHERE
message_body IS NOT NULL
GO

He sent me approx million+ records in “MessageNeedsToClose” and less then hundred count comes in “MessageToKeep”.

SELECT query before the UNION ALL clause checks where the row in transmission queue is having any message inside it or not. If Message_Body is NULL then there is no message at all. If “is_end_of_dialog” is 1 then that message is just an acknowledgment nothing else. So if there is no message to process in message_body and if message is just an acknowledgment, we can probably close it to fix the high number of queue one time, if message is already closed from initiator side.

Reader has already told me that transmission queue on initiator side (in server S1) always stays below 1000 and become zero also so there is no chance that the million message we have seen in S2 is still there open in S1.

Since I didn’t have access to his server to investigate further and the messages we have in target transmission queue is not a real business process, we can close that dialog. If I would have access to server, I would run profiler for broker in S1 as well as in S2 to see what is happening with that big transmission queue but the reader was already convinced and wanted to settled down by closing the conversation of dialog.

I have written one customized Stored procedure for him to create on Server S2 in the database where he has Service Broker setup.

CREATE PROCEDURE [CleanTransmissionQueueByEndingDialog]
AS
SET NOCOUNT ON
BEGIN
BEGIN TRY
DECLARE @ConvHandle UNIQUEIDENTIFIER

DECLARE conv CURSOR FOR

SELECT
S2TransQueue.conversation_handle
FROM
sys.conversation_endpoints AS S2ConvEnd WITH(NOLOCK)
INNER JOIN
sys.transmission_queue S2TransQueue WITH(NOLOCK)
ON
S2TransQueue.conversation_handle = S2ConvEnd.conversation_handle
AND S2TransQueue.message_body is null
AND S2TransQueue.is_end_of_dialog = 1
AND S2TransQueue.enqueue_time < getdate() - 7

--I HIGHLY recomment uncomment the following WHERE clause to check
--where same message is alive in initiator end or not
--if it is alive, we shouldn't close that message.

--WHERE NOT EXISTS(
-- SELECT 1
-- --S1 is a Linked Server, you have to replace it with your linked server of Initiator
-- --"DatabaseName" should be replace by your database name where you have setup
-- --Service Broker in Initiator Server.
-- FROM S1.DatabaseName.sys.conversation_endpoints S1ConvEnd with(nolock)
-- WHERE S1ConvEnd.conversation_id = S2ConvEnd.conversation_id
--)

OPEN conv
FETCH NEXT FROM conv INTO @ConvHandle

WHILE @@FETCH_STATUS = 0 Begin
END CONVERSATION @ConvHandle WITH cleanup
FETCH NEXT FROM conv into @ConvHandle
PRINT @ConvHandle
END

CLOSE conv
DEALLOCATE conv

END TRY

BEGIN CATCH
PRINT ERROR_MESSAGE()
END CATCH
END
GO

Please note that I have investigated this issue without accessing live server of blog reader. Whatever suggestions I have given here is going to affect your live production environment if you are executing this script in production environment so please give a second thought and do it at your own risk.

I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)
  • Get Transmission queue row count in Service Broker (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Get Transmission queue row count in Service Broker

Get Transmission queue row count in Service Broker

Sometime back I have written one small script (Click here to see that script) which was giving us total number of row in initiator as well as in target queue. It is good to keep watch on the queue count on initiator as well as on target queue but sometime it happens that initiator queue transmits message to the “Transmission Queue” but somehow “Transmission Queue” is not able to pass that message to Target Queue. If we have watch only on Initiator queue and target queue of service broker and don’t keep watch on transmission queue, we might fall under an impression that everything is fine and everything under control but situation might be opposite.

Hope you got an importance of transmission queue by now and wanted to see the TSQL which can get you the total row count (message to send) in transmission queue. Here is the user defined function which I used to use in order to get total row/message count in transmission queue.


CREATE FUNCTION GetTransmissionQueueCount()

RETURNS TABLE

AS

RETURN

SELECT

'Transmission Queue' AS QueueName,

Parti.Rows

FROM

sys.objects AS Obj

INNER JOIN

sys.partitions AS Parti

ON

Parti.object_id = Obj.object_id

WHERE

Obj.name = 'sysxmitqueue'

GO

As soon as you have above user defined function ready in the database where you have setup service broker, you can use that function with simple SELECT statement like this:


SELECT * FROM GetTransmissionQueueCount()

GO

If there is any message available in transmission queue of database, you will get value or else you will get zero.

I have written some other articles related to service broker, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)
  • Find Service Broker Queue Count in SQL Server (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Find Service Broker Queue Count in SQL Server

Find Service Broker Queue Count in SQL Server

Service broker is having bit complicated architecture so It may be little difficult to established service broker for beginners but it is little more difficult, even for experienced person, to keep watch on the health of Service Broker.

I have implemented Service Broker for many of my clients so far and have implemented monitoring system as well so that DBAs can keep watch on the health as well as pressure on the service broker. One of the primary watch I used to have is, count the total number of queue in initiator or target queue so that we come to know that which queue (target queue or initiator queue) is having a big burden. I you see high number in any of the queue and id doesn’t decreasing since long time, it is a time to panic and investigate more.

Now, one obvious question would comes in mind that how can we check the total count of each queue? Well, I have created one small user defined function by using “sys.objects” and “sys.partitions” catalog view.

By the way, before we see the user defined function I have created, if you are directly landed to this article and new to Service broker, have a look at following articles which covers Service Broker concept from scratch.

  • Importance of Service Broker in SQL Server (Click Here)
  • Introduction of Service Broker in SQL Server (Click Here)
  • Enable Service Broker in SQL Server database (Click Here)
  • Small demonstration of Service broker for beginners (Click Here)
  • Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server (Click Here)

Anyway, let us continue with the user defined function:

CREATE FUNCTION[dbo].[GetQueueCount]()
RETURNS TABLE
AS
RETURN
SELECT
queues.Name
, parti.Rows
FROM
sys.objects AS SysObj
INNER JOIN
sys.partitions AS parti ON parti.object_id = SysObj.object_id
INNER JOIN
sys.objects AS queues ON SysObj.parent_object_id = queues.object_id
WHERE
parti.index_id = 1
GO

Once you have Table Valued Function ready in the database where you have setup service broker queues, execute the function with simple SELECT query as given below.

SELECT * FROM [GetQueueCount]();
GO

Here is the screen capture of the result in my test database. You might get different queue name with different queue count based on the service broker you have setup.

QueueCount

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.