When you backup and restore a database to a different with messages in the
queue, you now have two copies of the active dialog which will both try to
send the same messages. This can cause data to be lost because messages
went to the wrong database. To prevent this, Service Broker is
automatically disable when you do a restore unless you specify in the
restore that you are not making a copy with the ENABLE_BROKER option of
restore. If you are making a copy, you can use the NEW_BROKER option in
restore which will get rid of any active dialogs in the copy to make sure
they don't interfere with the original. Since you have already done the
restore without these options, you need to run:
ALTER DATABASE Inventory SET NEW_BROKER
ALTER DATABASE Inventory SET ENABLE_BROKER

Signature
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
> hi all,
>
[quoted text clipped - 31 lines]
>
> select count(*) from tQueueSender
tin - 07 Sep 2006 18:46 GMT
GREAT! it works now!
but it didn't work initially when i first run the 2 alter statements. it
just keep executing for almost an hour, but cpu stayed at 0-1%. i tested the
messaging while it runs, and it didn't work. so i aborted it. restarted the
sql service. didn't work. then i dropped all the existing SB objects and
recreate them. then ran the alter, but it's still taking forever. but when
i tested it while it runs, it worked. so i aborted it. drop all SB objects
and recreated again, restarted sql. and it worked and stays working.
in short, i never get "command completed successfully" from the alter
statements. i was testing, dropping and recreating the SB objects while it
runs forever. but the alter completed instantly on other less active db.
anyhow, thanks again!
> When you backup and restore a database to a different with messages in the
> queue, you now have two copies of the active dialog which will both try to
[quoted text clipped - 46 lines]
> >
> > select count(*) from tQueueSender
Roger Wolter[MSFT] - 07 Sep 2006 18:52 GMT
The ALTER command takes a database lock so if anyone was logged into the
database, it would hang waiting for the lock. The most likely cause is you
having the database open in the query window when you run the command.
Switch to master or tempdb before you run it.

Signature
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
> GREAT! it works now!
>
[quoted text clipped - 73 lines]
>> >
>> > select count(*) from tQueueSender
tin - 07 Sep 2006 20:32 GMT
dude! u rock!
> The ALTER command takes a database lock so if anyone was logged into the
> database, it would hang waiting for the lock. The most likely cause is you
[quoted text clipped - 78 lines]
> >> >
> >> > select count(*) from tQueueSender