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.

[sourcecode language=”sql”]

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

[/sourcecode]

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:

[sourcecode language=”sql”]

SELECT * FROM GetTransmissionQueueCount()

GO

[/sourcecode]

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:

[sourcecode language=”sql”]
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
[/sourcecode]

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.

[sourcecode language=”sql”]
SELECT * FROM [GetQueueCount]();
GO
[/sourcecode]

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.

Find TCP IP port in SQL Server 2012 by sys.dm_tcp_listener_states DMV

Find TCP IP port in SQL Server 2012 by sys.dm_tcp_listener_states DMV

I have already explained different ways to find TCP port of SQL Server in one of my earlier article. We have already seen following different ways to find TCP port.

  • XP_RegRead extended stored procedure
  • From “SQL Server Configuration Manager”
  • XP_ReadErrorLog extended stored procedure

These are the traditional ways which has been in use since long but SQL Server 2012 provides you one direct way by providing “sys.dm_tcp_listener_states” DMV.

Let us try executing simple SELECT statement on “sys.dm_tcp_listener_states” DMV.

[sourcecode language=”sql”]

SELECT

listener_id,

ip_address,

is_ipv4,

port,

type_desc,

state_desc,

start_time

FROM sys.dm_tcp_listener_states WITH (NOLOCK)

GO

[/sourcecode]

Here is the screen capture I have received from my laptop when I executed above SELECT statement.

TCPIP

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 Object like Contract, Message Type, Service Name, Queue Name in SQL Server

Find Service Broker Object like Contract, Message Type, Service Name, Queue Name in SQL Server

After reading one of my recent article regarding “Small demonstration of Service broker for beginners”, one of the blog reader has asked me very interesting question. He asked me that though he has created all queues, message types etc. demonstrated in “Small demonstration of Service broker for beginners” article but how can he see list of all objects related to the particular contract? Is there any DMV which gives all these information?

I have created one small SELECT script based on the following system catalog view which caters the need of the question given above.

  • sys.service_contract_message_usages
  • sys.service_message_types
  • sys.service_contracts
  • sys.service_contract_usages
  • sys.services
  • sys.service_queue_usages
  • sys.service_queues

Here is the query I have created based on all the system catalog view given above.

[sourcecode language=”sql”]

SELECT

ServiceContract.name AS ‘ContractName’,

ServiQueue.name AS ‘QueueName’,

Servi.name AS ‘ServiceName’,

MessageType.name AS ‘MessageType’,

MessageUsage.is_sent_by_initiator,

MessageUsage.is_sent_by_target,

MessageType.validation,

MessageType.validation_desc

FROM sys.service_contract_message_usages AS MessageUsage

INNER JOIN sys.service_message_types AS MessageType ON MessageUsage.message_type_id =MessageType.message_type_id

INNER JOIN sys.service_contracts AS ServiceContract ON ServiceContract.service_contract_id =MessageUsage.service_contract_id

INNER JOIN sys.service_contract_usages ServContractUse ON ServContractUse.service_contract_id =ServiceContract.service_contract_id

INNER JOIN sys.services AS Servi ON Servi.service_id=ServContractUse.service_id

INNER JOIN sys.service_queue_usages AS SerQueueUse ON SerQueueUse.service_id = Servi.service_id

INNER JOIN sys.service_queues AS ServiQueue ON ServiQueue.object_id=SerQueueUse.service_queue_id

–you can query your own contract name or even remove WHERE clause

–to see complete list of Service Broker objects in database

WHERE ServiceContract.name like ‘%Extreme%’

ORDER By MessageType,QueueName

GO

[/sourcecode]

Here is the list I have received:

ServiceBrokerObjectList

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.

Small demonstration of Service broker for beginners

Small demonstration of Service broker for beginners

If you are new to Service Broker and want to know what service broker is and where can we use service broker, you can have a look at my two earlier articles.

Importance of Service Broker in SQL Server (Click Here)

Introduction of Service Broker in SQL Server (Click Here)

If your database is not already enabled for Service Broker, have a look at following link to know how to enable Service Broker for database in SQL Server.

Enable Service Broker in SQL Server database (Click Here)

Once you are ready with basic understanding of Service broker, we can, now, focus on developing architecture for service broker. This article will show you very basic architecture of service broker without much complexity so that even beginners can understand it easily, later on I will come up with some exciting and complex architecture of Service Broker.

Creating sample database and enable service broker for it.

[sourcecode language=”sql”]
USE MASTER
GO

CREATE DATABASE ExtremeAdviceSB
GO

ALTER DATABASE ExtremeAdviceSB SET Enable_Broker WITH ROLLBACK IMMEDIATE
GO
[/sourcecode]

Now, creating two different message type. One for request message for initiator service and another one for response message for target service.

Note: If you are not aware with “Message Type” and other service broker related terms, please click here.

[sourcecode language=”sql”]
USE ExtremeAdviceSB
GO

CREATE MESSAGE TYPE [http://Extreme-Advice.com/SBDemo01/RequestMessage]
VALIDATION = WELL_FORMED_XML
GO

CREATE MESSAGE TYPE [http://Extreme-Advice.com/SBDemo01/ResponseMessage]
VALIDATION = WELL_FORMED_XML
GO
[/sourcecode]

Now, we will create one contract which will define which message type will be used from initiator as well as from target.

[sourcecode language=”sql”]
CREATE CONTRACT [http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
(
[http://Extreme-Advice.com/SBDemo01/RequestMessage] SENT BY INITIATOR,
[http://Extreme-Advice.com/SBDemo01/ResponseMessage] SENT BY TARGET
)
GO
[/sourcecode]

Once, you are ready with Contract, it is a time to define two queue 1.) Initiator Queue 2.) Target Queue. Which will be used by core logic or service program. Initiator queue will generate message/request which will be sent to target queue after validation. Target queue will process message/request and generate response message and send to initiator queue.

[sourcecode language=”sql”]
CREATE QUEUE SBDemoInitiatorQueue
WITH STATUS = ON
GO

CREATE QUEUE SBDemoTargetQueue
WITH STATUS = ON
GO
[/sourcecode]

After having both the queue, need to define service which will work for the same contract we have created above in our example.

[sourcecode language=”sql”]
CREATE SERVICE SBDemoInitiatorService
ON QUEUE SBDemoInitiatorQueue
(
[http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
)
GO

CREATE SERVICE SBDemoTargetService
ON QUEUE SBDemoTargetQueue
(
[http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
)
GO
[/sourcecode]

Well now we are ready with basic architecture of service broker for our first demonstration for beginners. Here is the screen capture from my SSMS which shows every object I have created above. Have a look:

1ServiceBrokerObjectTree

We will generate two message from initiator and send it to target with “BEGIN DIALOG CONVERSATION” command.

[sourcecode language=”sql”]
–Message 1
BEGIN TRY
BEGIN TRANSACTION;

DECLARE @UniId UNIQUEIDENTIFIER
DECLARE @SBMessage NVARCHAR(MAX);

BEGIN DIALOG CONVERSATION @UniId
FROM SERVICE SBDemoInitiatorService
TO SERVICE ‘SBDemoTargetService’
ON CONTRACT [http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
WITH ENCRYPTION = OFF;
SET @SBMessage =
‘<SBRequest>
Message 1: Extreme-Advice.com on Service Broker
</SBRequest>’;

SEND ON CONVERSATION @UniId MESSAGE TYPE
[http://Extreme-Advice.com/SBDemo01/RequestMessage]
(
@SBMessage
);
COMMIT;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO

–Message 2
BEGIN TRY
BEGIN TRANSACTION;

DECLARE @UniId UNIQUEIDENTIFIER
DECLARE @SBMessage NVARCHAR(MAX);

BEGIN DIALOG CONVERSATION @UniId
FROM SERVICE SBDemoInitiatorService
TO SERVICE ‘SBDemoTargetService’
ON CONTRACT [http://Extreme-Advice.com/SBDemo01/ExtremeAdviceContractDemo]
WITH ENCRYPTION = OFF;
SET @SBMessage =
‘<SBRequest>
Message 2: Extreme-Advice.com on Service Broker
</SBRequest>’;

SEND ON CONVERSATION @UniId MESSAGE TYPE
[http://Extreme-Advice.com/SBDemo01/RequestMessage]
(
@SBMessage
);

COMMIT;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO
[/sourcecode]

Since we have generated two message from TSQL given above, we can confirm the same with “SBDemoTargetQueue” hidden table of target queue.

[sourcecode language=”sql”]
SELECT CAST(message_body as XML) AS Message, * FROM SBDemoTargetQueue
GO
[/sourcecode]

Here is the screen capture of above SELECT statement:

2TargetQueueHiddenTable

Now, we have to message received in target queue which we have seen above. Now, question is, how to process it?

Well, we have “RECEIVE” command to process the record by reading “SBDemoTargetQueue” hidden table. Here is the code which will process one record from target queue table. Since we have two records in target queue, we have to execute following TSQL two times to get both record processed.

[sourcecode language=”sql”]
DECLARE @ConvGroupID UNIQUEIDENTIFIER
DECLARE @ConvHandleID UNIQUEIDENTIFIER
DECLARE @messagetypename NVARCHAR(256)
DECLARE @messagebody XML;
BEGIN TRY
BEGIN TRANSACTION;

RECEIVE TOP (1)
@ConvGroupID = conversation_group_id,
@ConvHandleID = conversation_handle,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM SBDemoTargetQueue

IF (@@ROWCOUNT > 0)
BEGIN
PRINT ‘MessageType: ‘ + @messagetypename
PRINT ‘MessageXML: ‘ + CAST(@messagebody AS NVARCHAR(MAX))
END
COMMIT
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
GO
[/sourcecode]

Once you are done with both the message from target queue, you can confirm that there is nothing left in your target queue’s hidden table.

[sourcecode language=”sql”]
SELECT CAST(message_body as XML) AS Message, * FROM SBDemoTargetQueue
GO
[/sourcecode]

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.

Enable Service Broker in SQL Server database

Enable Service Broker in SQL Server database

If you are new to Service Broker and want to know what service broker is and where can we use service broker, you can have a look at my two earlier articles.

Importance of Service Broker in SQL Server (Click Here)

Introduction of Service Broker in SQL Server (Click Here)

Once you understand what Service Broker is and finally decides to implement it in your database, you have to enable Service Broker for you database. Let us have a look at it.

[sourcecode language=”sql”]
–Enable service broker in SQL Server database for Adventureworks2012 database
ALTER DATABASE Adventureworks2012 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

— TRUSTWORTHY database property is used to indicate whether the instance of SQL Server trusts the
–database and the contents within it. setting it ON if service broker is connecting outside database/instance
ALTER DATABASE Adventureworks2012 SET TRUSTWORTHY ON;
GO
[/sourcecode]

Once you enable service broker in your SQL Server database, you can confirm whether Service Broker enabled in your database or not. Execute following TSQL in your SQL Server instance.

[sourcecode language=”sql”]
SELECT is_broker_enabled,* FROM sys.databases
WHERE name=’Adventureworks2012′
GO
[/sourcecode]

If you get “1” in “is_broker_enabled” field in above SELECT query than you are fine as Service Broker is already enabled now.

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.

Introduction of Service Broker in SQL Server

Introduction of Service Broker in SQL Server

Before I actually start making you understand about what Service Broker is, I have explained some practical scenario where I have introduced Service Broker recently. If you would like to know those scenarios, have a look at my earlier article “Importance of Service Broker”.

Service Broker is an asynchronous message processing framework build directly into the SQL Server. Following are four important objects of Service Broker.

1.) Message Types

2.) Contracts

3.) Queue

4.) Service Program

Message Types:  Message Types is the first and one of the important objects of SQL Server Service Broker. Message Type do the important job of validating message before sending it to queue. There are four type of message types available.

  • XML Validation against XML Schema
  • Well Formed XML
  • No Validation (no validation at all, especially for binary data)
  • Empty (there is no message body required)

Contracts: Contracts decides which message type suppose to be used by Service Broker for a particular task. In service broker we used to send message to the other service to process the message and Contract decides message type suppose to be used between two services. If you send the message type from one service to another service which is not defined in contract, message will be rejected. You can easily find the message types being used from the sender to receiver by looking at Contract definition.

Queue: Queue is a native SQL Server database object and very important part of SQL Server service broker. Queue stores message (not matter whether it is from target service or initiator service).  When Service Broker receives a message, It first gets validated by Message Types and after successful validation check, message used get stored in queue for further process. Queue is like a SQL Server table where you can even query to see the message but can’t execute DML statements explicitly to manipulate data for queue table manually. Since queue is basically a table of SQL Server, it comes with SQL Server database backup and restore.

Service Program: Service Program used to process message from the queue. When a new message arrives, Service Broker automatically calls service program defined. If internal activation is used, SQL Server Stored Procedure can be the service program whereas, when external activation is used, you can have your own separate application for service broker’s service program.

Well, these are some basic objects with brief introduction of Service Broker. I will come up with more information and practical stuff related to Service Broker soon.

Till than Stay tuned and enjoy SQL!!!!!

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.

 

Importance of Service Broker in SQL Server

Importance of Service Broker in SQL Server

Sooner or later every business process needs some asynchronous processes to cater the business needs and keep their application free from the heavy processes which can be tedious for the user if it directly processed from the application. It may decrease the performance of the application.

There are many asynchronous processes (like MSMQ, Web services, WCF and many more) but Service Broker is one of my favorite if I have setup of SQL Server for the application.

Service broker manages the queue as a database object and hence we get native support of SQL Server so that if we take a backup of SQL Server database, queue will be backed up with it.

Let me give you some situation where I have recently set up Service Broker.

I was working with one home health care management system of New York. Many agencies working with that SAAS (Software As A Service) product and we used to find thousands of concurrent user actively using that application in business hours. Every Wednesday each agency used to generate Invoice and billing for the insurance company as well as for staff because Friday is a payment day so invoice, pay slip etc. should be ready and calculated on Wednesday.

We used to get heavy database & network pressure at that time, out application keeps hanging and it shows time out error to many of the agencies too which is very annoying. We used to help them by creating invoices/bills manually directly from the backend but it is not good to approach client like this so we have finally decided to process all these calculations in form of service broker, as soon as client generates the request, we call Service broker and as soon as Service Broker completes process, it used to send an email to client so that they can get their bills/invoices from the application without making a pressure in application and getting timeout error.

This is really a power of asynchronous message processing.

I would like to share one more business requirement which forced me to set up service broker recently.

I was in involved in one project of one of the environmental laboratory in New Jeary, USA where they used to perform test for Air, Soil and Water. They have many different types of test used to perform on different types of samples. They define a special way to perform the test based on government norms as well as client’s demand. That special way (chemical to process the test) needs a combination of many different chemicals and other required stuff. Once they define this way, they named it “Recipe”. One Recipe may have many different chemicals as well as other Recipe too. While generating the report of Recipe, we needs to go infinite inside the content of Recipe as it may be single chemical or other Recipe and that other Recipe could be created from other Recipe. This Recipe report and calculation take too much time and hence intranet based .NET web application used to give time out error so many times and user gets frustrate so in this situation we have decided to use Service Broker so that user can put request for Recipe report from .NET application, Service Broker completes the process and send an email to user about completion.

This is how Services broker can be used. I will come up with one more article based on this topic very soon with the introduction of important part of Service Broker… stay tuned!!!

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.

Dealing with size of sysmail_mailitems table in MSDB database

Dealing with size of sysmail_mailitems table in MSDB database

EmailIssueBy providing database mail functionality, Microsoft has given us enterprise level email functionality and I like this very much. Today one of my friend sent me chat request and asked me whether I can help him with email issues in his SQL Server as he knows that I am using this functionality since long and I always appreciate fast performance of the same.

My friend told me that his email functionality was working just fine but suddenly it stops working. Since, it was production environment, he didn’t had much time to investigate so my WILD thought for him to execute following SP in MSDB database.

[sourcecode language=”sql”]

exec msdb.dbo.sysmail_start_sp

GO

[/sourcecode]

He executed above given command immediately and after few minutes he ping me back and said thanks as email has started working.

“dbo.sysmail_start_sp” stored procedure is in the msdb database. This stored procedure starts the Database Mail queue that holds outgoing message requests and enables the Service Broker activation for the external program. It looks like, somehow, his email got stopped which is now started by executing the SP.

Since my friend was now tension free as his database mail has started, I asked him, just because of curiosity, what is the size of his “sysmail_mailitems” table in MSDB database and how many rows are there. I have also provided script to get these details which I have already provided in my blog earlier. Here are the links, if you are interested:

  • Calculate Table & Schema size in SQL Server (Click Here)
  • List of Table in Filegroup with table size in SQL Server (Click Here)
  • Get row count for tables quickly in SQL Server (Click Here)

He come back with an answer, size of “sysmail_mailitems” table was 58+ GB and total number of rows found are approx 1 million. I can’t imagine MSDB database with 50+GB size. We must have to reduce the size. He had status of email stored in “sysmail_mailitems” table for more then year. Most of them are already delivered so there is no need to keep that record. I suggested him to clean “sysmail_mailitems” table if older messages are not in use in his business logic or not useful anywhere.

[sourcecode language=”sql”]

USE MSDB
GO

DECLARE @CutOffDate DATETIME
–suppose I want to delete records which are older then 3 month

SET @CutOffDate=Dateadd(d, -90, Getdate())

exec sysmail_delete_mailitems_sp
@sent_before = @CutOffDate;

exec sysmail_delete_log_sp
@logged_before = @CutOffDate;
GO

[/sourcecode]

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.