Monday, June 23, 2008 6:37 AM
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:56 AMModeratorYou 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.
Tuesday, June 24, 2008 6:15 AM
Saturday, June 28, 2008 8:10 PM
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
Sunday, June 29, 2008 7:30 AMModeratorhttp://msdn.microsoft.com/en-us/library/ms166044.aspx
Tuesday, July 01, 2008 9:50 PM
These details i got from sys_transmission_queue .Let mw know how to go about
One or more messages could not be delivered to the local service targeted by this dialog.
Wednesday, July 02, 2008 6:30 AMModeratorThe 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.
Thursday, July 03, 2008 10:22 PM
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"
Friday, July 04, 2008 1:29 PMModeratorHow 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.
Monday, July 07, 2008 4:20 PM
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.
Wednesday, July 09, 2008 9:05 AMModerator
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).
Thursday, August 07, 2008 11:47 PM
Microsoft SQL Server 2005 - 9.00.3205
Friday, August 08, 2008 11:47 PM
Set the msdb to single user before purge the queue:
ALTERDATABASE [msdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTERDATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE
ALTERDATABASE [msdb] SET MULTI_USER
Sunday, October 12, 2008 5:09 PM
Sunday, October 12, 2008 5:11 PMIs 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
Tuesday, February 24, 2009 12:44 AMHello,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 spacemsdb 331125.56 MB 330361.18 MBreserved data index_size unused25480 KB 12240 KB 6344 KB 6896 KBRunning "sp_helpfile MSDBData" results in:name filename filegroup size maxsize growth usageMSDBData ...\MSDBData.mdf PRIMARY 338315328 KB Unlimited 80 KB data onlyI 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.