How to purge sysxmitqueue from msdb

  • shahmm

    Ten Centuries

    Points: 1183

    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.

  • KenpoDBA

    SSCrazy Eights

    Points: 9801

    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

    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: 9801

    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: 2458

    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

    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: 2458

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

  • shahmm

    Ten Centuries

    Points: 1183

    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: 2458

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

  • MannySingh

    SSChampion

    Points: 12465

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

  • rajganesh.dba

    SSCrazy

    Points: 2458

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

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

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