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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.