Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / Other Technologies / Service Broker / September 2006

Tip: Looking for answers? Try searching our database.

service broker stopped working

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
tin - 07 Sep 2006 16:13 GMT
hi all,

we have a messaging system using the service broker.  code and setup is the
same on dev and live db.  a while ago, i had backed up the live db, and
restored it on dev.  messaging on dev stopped working soon after.  not too
sure how that relates, but that's the only changed to the db/system.

after some debugging, i found that the server just doesn't send the message
to the queue/service, but it says successful and no error.  but there's
nothing in the queue.  and i sure nothing else picked it up.  it ran the
script below on the db which got restored on dev.  also ran it on existing
DBs.  also newly created db.  results are all the same.  the count(*) on the
queue returns 0.  any ideas???  thank you very much in advance!!!

----------------------------------------------------------------------
create message type tMessage validation = none
create contract tContract (tMessage sent by initiator)
create queue tQueueSender
create service tServiceSender on queue tQueueSender
create queue tQueueReceiver
create service tServiceReceiver on queue tQueueReceiver (tContract)

declare @conversationhandle uniqueidentifier;
begin dialog @conversationhandle
from service [tServiceSender]
to service 'tServiceReceiver'
on contract [tContract]
with encryption = off;
send on conversation @conversationhandle
message type [tMessage] ('blah blah blah')
             
select count(*) from tQueueSender
Roger Wolter[MSFT] - 07 Sep 2006 16:57 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.