November 13, 2009 at 5:46 am
ALTER DATABASE <db_name> SET NEW_BROKER WITH ROLLBACK IMMEDIATE
Hi,
A client is asking us to run the above command on their production database with the intention of enabling the Service broker. As I have no experience of the Service Broker service, could anyone tell me possible consequences of running this command during
The technician that has made the request has commented that when he ran the command in their test environment that it took a few seconds to run and that during the time the few connections that were made to the database where lost. Can anyone confirm whether this should be expected?
Regards,
AtlasAdmin
November 13, 2009 at 8:35 am
Based on BOL:
NEW_BROKER
Specifies that the database should receive a new broker identifier. Because the database is considered to be a new service broker, all existing conversations in the database are immediately removed without producing end dialog messages.
I read that to just create a new identifier and while it is doing it to kill any connections to the DB..
I see the only risk being if there are already service brokers setup to use that DB they will probably break, and any live connections will be killed and rollback..
CEWII
November 16, 2009 at 1:50 am
Thanks Elliot,
I've run the following T-SQL script on one of the cluster nodes and the database in question (EYAPPDB_LIVE) doesn't seem to have the Broker Service enabled.
Can I assume from what you say that enabling the Broker Server for this database will not force a kill of all connected connections.
Regards,
AtlasAdmin
November 16, 2009 at 2:16 am
ALTER DATABASE <db_name> SET NEW_BROKER WITH ROLLBACK IMMEDIATE
...will roll back any transactions which prevent the command from completing immediately. In this case, it would also break the connection since this change requires an exclusive database lock.
ALTER DATABASE <db_name> SET NEW_BROKER WITH ROLLBACK AFTER 10 SECONDS
...will wait up to ten seconds if it cannot acquire the locks it needs. Then it will roll back the blocking connections, disconnecting them from the database.
ALTER DATABASE <db_name> SET NEW_BROKER;
..will wait indefinitely until it is able to complete. This might be never.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 16, 2009 at 2:26 am
hmm... did'nt kknow about rollback after. thanks
"Keep Trying"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply