How to purge sysxmitqueue from msdb

Question

Post reply

How to purge sysxmitqueue from msdb

shahmm

Ten Centuries

Points: 1183
More actions
May 16, 2008 at 8:31 am

#207397

I have msdb size grown to 5+GB, with the table sysxmitqueue using 4.4+GB. I found that previous dba used service broker feature and it is not used at present. I have dropped the service and queue. The problem is I am not able to clean this table. As this msdb database resides on c drive, I would like to clean this table. If any one knows how to truncate/delete rows please help.
thanks in advance.
I am running sql2005 64bit sp2 on WINDOWS SERVER 2003 AMD64.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

Do you get an error message when you try to delete?

I'm assuming so since you're trying to modify a system table.

You can modify system tables in Yukon if you log in using the DAC.

So you'll connect like this:

ADMIN:username

That should allow you to modify the system table. If not, then you may have to throw the system into single user mode first... I can't remember, but I don't think you should have to do that.

Anyway, failing anything else, you can always move msdb to a diff. partition.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

shahmm Ten Centuries Points: 1183 More actions · Answer 2

I tried everything I can to remove/delete rows from that table but no help. When I try to delete/truncate I get the following error message

Cannot find the object "sysxmitqueue" because it does not exist or you do not have permissions.

I have moved finally to different drive but still would like to clean it up.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 3

Yeah, I'm not familiar with that table, but login with DAC and see what you can do that way.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

rajganesh.dba SSCrazy Points: 2538 More actions · Answer 4

Hi ,

Have u found any way to shrink sysxmitqueue table belonging to msdb database since i have already moved msdb to another drive

shahmm Ten Centuries Points: 1183 More actions · Answer 5

No. But after I deleted the service broker queue which was not used, it cleaned up automatically. Not suer what triggered that.

rajganesh.dba SSCrazy Points: 2538 More actions · Answer 6

Can u pls tell me wat command you used , since its a prod machine , one mistake and the consequence 🙂

shahmm Ten Centuries Points: 1183 More actions · Answer 7

DROP QUEUE YourQueueName;

Before you drop queue, you have to drop service (DROP SERVICE YourServiceName) attached to that queue so use caution and do your research.

rajganesh.dba SSCrazy Points: 2538 More actions · Answer 8

Sorry any document , my company not accepting to touch production server without any valid documentation :crying:

MannySingh SSChampion Points: 12465 More actions · Answer 9

Good Post.. Service Broker has that issue or a Behaviour, and can increase the size of your msdb...

Maninder
www.dbanation.com

rajganesh.dba SSCrazy Points: 2538 More actions · Answer 10

Anyone with kb or article or document to solve this issue pls act .Thanks in advance