Yesterday, I introduced the 12 Days of pre-Christmas. In that first article, I gave you a gift to help maintain your msdb undercarriage. Today I want to continue in that vein.
As is the tradition with the 12 Days of Christmas, each of these gifts should provide more value as we go. The value may not be that the gift I share is of any more value than a previous gift, but that in combination with the previous gifts more value will be gained. Woot!
On the Second Day of Pre-Christmas…
My DBA gave to me some lost messages. After fixing the issue with the maintenance plan log tables you still notice that you have a large msdb database. Through further investigation you can see that the cause is related to the sysxmitqueue table as displayed in the attached image.
The sysxmitqueue table is a queue table for Service Broker messages. If this table is growing, then you have some undeliverable messages. This means that something with Service Broker is mis-configured. Too many of these messages start building up and the size can get relatively large for this table.
Once the messages start to queue up, you need to investigate. Start with querying the sys.transmission_queue system catalog view. In that view there is a transmission_status column. If you get a message like “The Service Broker in the target database is unavailable: ‘The service broker is administratively disabled.’.”, then you have an issue with Service Broker not being properly enabled in at least one of the databases involved in Service Broker.
If you have that message, you can confirm that Service Broker is not properly setup in one of the databases by running the following query from each database involved in Service Broker.
SELECT is_broker_enabled FROM sys.databases
WHERE database_id = DB_ID() ;If you have inherited something like this, and nobody has noticed that it isn’t working – you may not need it enabled and may need to look at removing the components for the queue associated with the failed messages. Check with interested parties and determine if the queue and service are needed or not. In the meantime, you can clear out some of these messages by running the following.
END CONVERSATION 'conversation_handle' WITH CLEANUP;In this code, conversation_handle is the handle retrieved from the sys.transmission_queue system catalog view. Once this completes, you should see the that the number of rows is greatly reduced in sysxmitqueue as shown here.
Notice here that the rows have been reduced to 0. At this time, the space has yet to be released from the table. This is something you may have noticed from purging the maint plan logs as discussed yesterday.
Coming Up
In the next few posts, we will discuss some more undercarriage work for the msdb database. In addition, we will discuss releasing this space.
