none
How to purge sysxmitqueue table in msdb - SQL server 2005

    Question

  •  

    In SQL server 2005 - msdb database grows huge because sysxmitqueue table,  does anyone know how to shrink and solve this issue permanently  without  recurrence .

     

     Complete document to clear out this issue will be helpful since its directly affecting production environment .

     

     Already moved msdb database to separate drive( Database mirroring is happening in that server . FYI)

     

     Thanks in advance.

    Monday, June 23, 2008 6:37 AM

Answers

  • You have an application that is sending messages in msdb and the destination service is incorrectly configured. Look in msdb.sys.transmission_queue and check the messages source and destination service, message types and transmission_status, then ask your application guys who is sending those messages. Is weird that no application is complaining about those messages not arriving in the first place.

    If the messages are originating from the Event Notifications service it means that server level event notifications where created and the destination service is probably unreachable (most likely incorrectly configured, specially if you mention mirroring). 

    After you identify the culprit, you have two fix service configuration so that messages flow through, this will quickly drain the table.

    To completely obliterate the messages without attempting delivery use ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;. It is very important that you attempt this only if you have SP2 installed. If you do not fix the application that is sending those messages in the first place the database will grow back though.
    Monday, June 23, 2008 6:56 AM
    Moderator

All replies

  • You have an application that is sending messages in msdb and the destination service is incorrectly configured. Look in msdb.sys.transmission_queue and check the messages source and destination service, message types and transmission_status, then ask your application guys who is sending those messages. Is weird that no application is complaining about those messages not arriving in the first place.

    If the messages are originating from the Event Notifications service it means that server level event notifications where created and the destination service is probably unreachable (most likely incorrectly configured, specially if you mention mirroring). 

    After you identify the culprit, you have two fix service configuration so that messages flow through, this will quickly drain the table.

    To completely obliterate the messages without attempting delivery use ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;. It is very important that you attempt this only if you have SP2 installed. If you do not fix the application that is sending those messages in the first place the database will grow back though.
    Monday, June 23, 2008 6:56 AM
    Moderator
  • unTrang Web nay coi cung hay, vao coi thu di http://nhatquanglan.xlphp.net/ 

     

    I will check and update you.

     

     

    Trang Web nay coi cung hay, vao coi thu di http://nhatquanglan.xlphp.net/ 

     

     

     

     

    Tuesday, June 24, 2008 6:15 AM
  • Hi Remus Rusanu or anyone  ,

     

     Can u please provide me knowledge base or some other article ?  since this is going to be corrected in Production environment so proper documentation required

    Saturday, June 28, 2008 8:10 PM
  • http://msdn.microsoft.com/en-us/library/ms166044.aspx
    Sunday, June 29, 2008 7:30 AM
    Moderator
  •  

    These details i got from sys_transmission_queue .Let mw know how to go about


    from_service_name:
    *****************
    http://schemas.microsoft.com/SQL/Notifications/EventNotificationService

    to_service_name:
    *****************
    LoginService

    to_broker_instance:
    *******************
    10061A28-78A0-4DA5-8689-27432C12D520

    service_contract_name:
    ***********************
    http://schemas.microsoft.com/SQL/Notifications/PostEventNotification

    message_type_name:
    ********************
    http://schemas.microsoft.com/SQL/Notifications/EventNotification

    transmission_status:
    *******************
    One or more messages could not be delivered to the local service targeted by this dialog.

    Tuesday, July 01, 2008 9:50 PM
  • The messages are Event Notifications created with CREATE EVENT NOTIFICATION ... ON SERVER...
    Check the notifications type on sys.server_event_notifications. 
    Check in sys.databases which one is the database with service _broker_id  "10061A28-78A0-4DA5-8689-27432C12D520". Is the mirrored database by any chance?
    You will also need to know what application is using event notifications in your environment.
    Wednesday, July 02, 2008 6:30 AM
    Moderator
  • select * from msdb.sys.server_event_notifications:

    name        parent_class_desc        service_name broker_instance creator_sid
    Notify_Logins       SERVER         LoginService 1                                 0061A28-78A0-4DA5-8689-27432C12D520 

     

    I have copied the columns which can be considered and

    select * from sys.databases where service_broker_guid ='10061A28-78A0-4DA5-8689-27432C12D520':

     

    it leads to msdb database only  "I am unable to paste the output here"

     

     

    Thursday, July 03, 2008 10:22 PM
  • How about sys.databases with broker Id 0061A28-78A0-4DA5-8689-27432C12D520 ?

    Someone has subscribed to Notify_Logins events on server so a messages is being sent every time a connection login occurs. The notifications are sent to a service named "LoginService 1" in the database with broker id 0061A28-78A0-4DA5-8689-27432C12D520. I think you have plenty of information to track down the application/person that had created the subscription.
    Friday, July 04, 2008 1:29 PM
    Moderator
  •  

    Is there another way to clean up the sysxmitqueue besides

    DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE

     

    When I run this, it gets blocked.

     

    I do believe I have removed the procedure and the event that was causing the problem, but I am unable to clean up the sysxmitqueue.  I also have no space available on the drive where msdb sits.

     

    Debbie

    Monday, July 07, 2008 4:20 PM
  • Are you on SQL Server 2005 SP2 or prior to SP2? Implementation of NEW_BROKER prior to SP2 is rather slow and may cause issues on a very large number of conversations (millions).

    Wednesday, July 09, 2008 9:05 AM
    Moderator
  • Microsoft SQL Server 2005 - 9.00.3205

    SP2

    Thursday, August 07, 2008 11:47 PM
  • HI

     

    Set the msdb to single user before purge the queue:

     

    ALTER DATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

    GO

    ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE

    GO

    ALTER DATABASE [msdb] SET MULTI_USER

    GO

     

     

    MCDEBIL

    Friday, August 08, 2008 11:47 PM
  •  

    But db mirroring is happening regularly  if msdb is touched the it will be affected , All dbs are huge in size reconfiguring mirroring is time consuming
    Sunday, October 12, 2008 5:09 PM
  • Is there any way to shrink log files of mirrored db without disabling it , since log files sizes grows around 600GB leading to lack of space in disk

     

    Sunday, October 12, 2008 5:11 PM
  •  Hello,

    I had the same issue with SYSXMITQUEUE filling up with undelivered messages. I was able to use:

    ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

    to clear all the messages in SYSXMITQUEUE. 

    I ran "EXEC sp_spaceused" on the MSDB database and received the following:

    database_name database_size unallocated space
    msdb 331125.56 MB 330361.18 MB

    reserved data index_size unused
    25480 KB 12240 KB 6344 KB 6896 KB

    Running "sp_helpfile MSDBData" results in:

    name filename filegroup size maxsize growth usage
    MSDBData      ...\MSDBData.mdf PRIMARY 338315328 KB Unlimited 80 KB data only

    I tried using ShrinkFile to minimize the MSDB data file size, but that does'nt work. 

    I found out that the initial size of the MSDB data file size has been modified to 330,387 MB! When I installed SQL Server, I had set it to 250MB. So, I would like to shrink the file back to 500 MB. Could someone please let me know how to go about doing that?

    PS: 
    1. Running "ALTER DATABASE MSDB MODIFY FILE(NAME = MSDBData, SIZE = 500MB)" results in the error: "MODIFY FILE failed. Specified size is less than current size."
    2. Running "DBCC SHRINKFILE ('MSDBData', 500, EMPTYFILE)" results in the error: "Parameter 3 is incorrect for this DBCC statement." I had created another data file (MSDBData1) under the PRIMARY filegroup hoping to transfer contents from MSDBData into MSDBData1.







    Kas
    Tuesday, February 24, 2009 12:44 AM