Ok...I am going to answer my own post

I worked with MS on this all day and this caused quite a mess.

First...

I am not quite sure how this mess started but I am sure it had to do with the order in which I tried to back out the Queue.

In the end I ended up with a ton of messages in my local queue and 17 gig worth of messages in the msdb..sysxmitqueue table.

To delete the Service, I had to increase the TLOG size to 20+ gig in the MYDBADB.

I am guessing that the TLOG on the MYDBADB needs to be larger than the size of the sysxmitqueue in the msdb database. Even thoug the destination queue resides in a user DB it still seems to have some roots in msdb :ermm: You can derive the size of the table by running the following in msdb:

SELECT TOP 100 OBJECT_NAME([object_id]), *

FROM sys.dm_db_partition_stats

WHERE index_id IN (0,1)

ORDER BY in_row_reserved_page_count DESC

Once I did this I was able to first disable the broker in the MYDBADB and then drop the service and queue.

Full procedure should have been...

use mydbadb

go

ALTER DATABASE MYDBADB SET DISABLE_BROKER

GO

ALTER QUEUE DBA_AuditLoginQueue WITH STATUS = Off ;

go

drop EVENT NOTIFICATION DBA_AuditLogin_Event_Notification

ON SERVER

go

drop ROUTE DBA_AuditLoginRoute

go

drop SERVICE DBA_AuditLoginService

go

drop QUEUE DBA_AuditLoginQueue

goGO

Now I am still left with a sysxmitqueue table in msdb that has 17+gig or 4+million orphan rows.

There is no easy way (that I am aware of) to remove these orphan rows without putting msdb into single user mode.

In my case, the msdb primary datafile grew out of proportion and we were forced to create a new datafile on a separate drive and disable autogrowth on the first file.

In my situation, our application is highly dependent on msdb so I will need to wait until a scheduled outage to clean this mess up.

The following procedures have not been tested but I believe this will clean up my orphan rows from the sysxmitqueue table.

1 - Shut down the SQL Server Agent

2 - Put msdb into single user mode

3 - run the following command to purge the table:

ALTER DATABASE [msdb] SET NEW_BROKER WITH ROLLBACK IMMEDIATE;

I will not be running this procedure until mid-April 2010. I will post the outcome.

In the meantime, please post if anyone can enlighten me as to what I did wrong originally.

I hope this comes of help to someone...it is a little cryptic