Both queues and durable topic subscriptions can build up messages in the database. If no client is reading from the queue, or no client connects to read from a durable subscription, those lists of messages continue to grow without bound.

The administrator should periodically check the JMS system to see if there are any queues or durable subscriptions that are growing in this manner. If so, the administrator should contact the application developers to see if their applications are behaving correctly. If necessary, the administrator might wish to remove the messages in those queues and durable subscriptions.

You can check the number of entries in a queue or a durable subscription using the SQL-JMS Administration Interface. You can also check these statistics using SQL, as described below.

Measuring a Queue

The following SQL statements select all entries in the queue named fooQueue. Counting these entries gives the current size of the queue:

SELECT msg_id
  FROM dms_queue_entry
  WHERE queue_id IN (SELECT queue_id
                       FROM dms_queue
                       WHERE queue_name = 'fooQueue')
Measuring a Durable Subscription

The following SQL statements select all entries in the durable subscription named fooSubscriber. Counting these entries gives the current size of the durable subscription.

SELECT msg_id
  FROM dms_topic_entry
  WHERE subscriber_id IN (SELECT subscriber_id
                            FROM dms_topic_sub
                            WHERE subscriber_name = 'fooSubscriber')
 
loading table of contents...