" noWrap width="1%" bgColor=#b71c0c>1. Yosi 29 Temmuz 12:19 seçenekleri göster Gönderen: "Yosi" <yus...@bizitek.com> - Bu yazarın yazdığı mesajları bul Tarih: Fri, 29 Jul 2005 02:19:00 -0700 Konu: Service Broker Cevapla | Yazara Cevap Ver | İlet | Yazdır | Sadece Mesaj | Aslını göster | Kaldır | Kötüye Kullanımı Bildir
I'm trying the service broker features of SQL Server 2005. I created a
Hi There
I have sent messages and they are all sitting in the transmission queue with a blank status, why is service broker not trying to send them ? They are no errors in the sql log. BOL says this is blank when it has not tried to send the message ? Service broker is definately activated in the database.
How do i force sql server to send anything in the transmission que ?
I have no idea what is wrong or where to check ?
Thanx
My normal scenario is tills sending live slaes to head office via service Broker queue. Sales are sent as soon as sale is made(normally tills on adsl lines), a loss of a Link to head office will still allow a sale to be made with the sale sitting in the transmission queue on the till but has not been commited at HO to adjust stock etc, until the link is back up. My worry is link goes down with several sales siting in the queue and then hardware failure. Is their a mechanism to backup the Transmission queue in the case of no link?
If you could back up the transmission queue at a till could i then take those messages and copy them into head offices queue for processing, this is in the case of my link to the till was down for a whole day but needed the daily sales from the shop,could i get the back up at end of the day and apply the messages at head office?
Any infomation on what would be the best way to recover from this event would be gratefully accepted.
any examples on a heartbeat to check the link status that is of very low cost between HO and tills bearing in mind HO needs to maintain status for large number of tills.
My transmission queue has lots of messages that will never, ever be delivered because the transmission_status = "The session keys for this conversation could not be created or accessed. The database master key is required for this operation."
How can I purge the transmission queue to get rid of this junk?
I have a strange problem with messages getting stuck in a transmission queue. The set up is as follows:
1. Client database behind physical firewall
2. Firewall rules forward inbound traffic to Client Database
3. Server database has route to firewall
4. Client and Server databases on different physical networks
Over the last couple of days, the IP address of the PC that has the Client database on it changed. Messages were getting through to the Server from the Client but not the other way round. I presume this also meant that message acknowledgements were also not getting back to the Client because messages were stuck in the Client transmission queue.
Looking in more detail at the queued messages at the Server side, it appeared that the Server had received the messages, processed them, and sent back an end conversation. The state of the conversation in conversation_endpoints was DISCONNECTED OUTBOUND. Which I assume means that these would never get through to the Client?
What is the correct way of dealing with this situation? I tried doing an end conversation with cleanup at the Server end but this is clearly wrong because this removes any trace of the conversation from the Server and means that the messages now get through correclty (again) and processed (again). Meaning that we now have duplicate messages in our database.
Any help would be much appreciated.
Thanks.
I have a web application that sets up a query notification on a queue. Normal operation works fine, but for some reason the queue being monitored disables occasionally (even with the transaction, error message, and end dialog message) pattern set up as described in Remus' blog post. When the queue disables, everything goes insane and the transmission queue, left unchecked, fills up the transmission queue with hundreds of thousands of PostQueryNotification messages. Am I doing something wrong? What should I do to mitigate this because it IS possible that the queue might disable sometimes, and I don't want drive space eaten up on a low-end SQL Express machine, if it does. Also - second question - where is the transmission queue storing all these messages? TempDB?
I want to use SSB as a mean to thread multiple SP invocation of the same procedure.
When I launched the initator processes it created in the transmission queue error messages saying for each of them that it is an invalid target service name.
The view (sys.transmission_queue) records them in this way in the status column:
"The target service name could not be found. Ensure that the service name is specified correctly and/or the routing information has been supplied."
Services and queues and activation procedure are all defined into the same database.
I drop services and queues, and i tried to create a service with the initial wrong name. Nothing happened.
Thanks vour your help
Hello,
is there built-in support for monitoring the number of elements in the transmission queue via performance counters?
Thanks,
I have a question about SQL Service Broker.
Here are the steps I use to produce the issue.
1) I drop the Service Broker on the Receiver by running the following sql script:
if exists (select * from sys.services where name = N'//TyMetrix360Audit/DataWriter') drop service [//TyMetrix360Audit/DataWriter]
2) I send some messages using the SQL Service broker on the sender side
3) The messages I send stay in sender transmission queue. Here is an example of what my transmission queue looks like after running select * from sys.transmission_queue on the sender.
02C54400-309C-DC11-8EED-0002B3D9F7B5 //TyMetrix360Audit/DataWriter 386DDD04-7E55-466A-BE83-37EFC20910B9 tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender //TyMetrix360Audit/Contract 2007-11-26 14:58:10.207 0 //TyMetrix360Audit/Message 0 0 0x3C003F0078006D006C002000760065007200730069006F006E003D00220031002E0030002200200065006E0063006F00640069006E0067003D0022007500740066002D003100360022003F003E000D000A003C0045006E0074006500720070007200690073006500410075006400690074004400610074006100200078006D006C006E0073003A007800730069003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002D0069006E007300740061006E00630065002200200078006D006C006E0073003A007800730064003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D00610022003E000D000A00200020003C005400610062006C0065004E0061006D0065003E004E004500540057004F0052004B003C002F005400610062006C0065004E0061006D0065003E000D000A00200020003C005000720069006D0061007200790043006F006C0075006D006E004E0061006D0065003E006E006500740077006F0072006B005F00690064003C002F005000720069006D0061007200790043006F006C0075006D006E004E0061006D0065003E000D000A00200020003C004F007000650072006100740069006F006E003E0055003C002F004F007000650072006100740069006F006E003E000D000A00200020003C004400620055007300650072003E00640062006F003C002F004400620055007300650072003E000D000A00200020003C004400610074006100620061007300650020002F003E000D000A00200020003C0044006100740061003E000D000A0020002000200020003C00780073003A0073006300680065006D0061002000690064003D0022004E006500770044006100740061005300650074002200200078006D006C006E0073003D0022002200200078006D006C006E0073003A00780073003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002200200078006D006C006E0073003A006D00730064006100740061003D002200750072006E003A0073006300680065006D00610073002D006D006900630072006F0073006F00660074002D0063006F006D003A0078006D006C002D006D007300640061007400610022003E000D000A002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D0022004E00650077004400610074006100530065007400220020006D00730064006100740061003A004900730044006100740061005300650074003D0022007400720075006500220020006D00730064006100740061003A004D00610069006E0044006100740061005400610062006C0065003D0022004400610074006100220020006D00730064006100740061003A00550073006500430075007200720065006E0074004C006F00630061006C0065003D002200740072007500650022003E000D000A00200020002000200020002000200020003C00780073003A0063006F006D0070006C006500780054007900700065003E000D000A0020002000200020002000200020002000200020003C00780073003A00630068006F0069006300650020006D0069006E004F00630063007500720073003D0022003000220020006D00610078004F00630063007500720073003D00220075006E0062006F0075006E0064006500640022003E000D000A002000200020002000200020002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D002200440061007400610022003E000D000A00200020002000200020002000200020002000200020002000200020003C00780073003A0063006F006D0070006C006500780054007900700065003E000D000A0020002000200020002000200020002000200020002000200020002000200020003C00780073003A00730065007100750065006E00630065003E000D000A002000200020002000200020002000200020002000200020002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D0022006E006500740077006F0072006B005F00690064002200200074007900700065003D002200780073003A0069006E007400220020006D0069006E004F00630063007500720073003D0022003000220020002F003E000D000A002000200020002000200020002000200020002000200020002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D0022006E006500740077006F0072006B005F006E0061006D0065002200200074007900700065003D002200780073003A0073007400720069006E006700220020006D0069006E004F00630063007500720073003D0022003000220020002F003E000D000A0020002000200020002000200020002000200020002000200020002000200020003C002F00780073003A00730065007100750065006E00630065003E000D000A00200020002000200020002000200020002000200020002000200020003C002F00780073003A0063006F006D0070006C006500780054007900700065003E000D000A002000200020002000200020002000200020002000200020003C002F00780073003A0065006C0065006D0065006E0074003E000D000A0020002000200020002000200020002000200020003C002F00780073003A00630068006F006900630065003E000D000A00200020002000200020002000200020003C002F00780073003A0063006F006D0070006C006500780054007900700065003E000D000A002000200020002000200020003C002F00780073003A0065006C0065006D0065006E0074003E000D000A0020002000200020003C002F00780073003A0073006300680065006D0061003E000D000A0020002000200020003C006400690066006600670072003A0064006900660066006700720061006D00200078006D006C006E0073003A006D00730064006100740061003D002200750072006E003A0073006300680065006D00610073002D006D006900630072006F0073006F00660074002D0063006F006D003A0078006D006C002D006D00730064006100740061002200200078006D006C006E0073003A006400690066006600670072003D002200750072006E003A0073006300680065006D00610073002D006D006900630072006F0073006F00660074002D0063006F006D003A0078006D006C002D0064006900660066006700720061006D002D007600310022003E000D000A002000200020002000200020003C0044006F00630075006D0065006E00740045006C0065006D0065006E0074003E000D000A00200020002000200020002000200020003C00440061007400610020006400690066006600670072003A00690064003D00220044006100740061003100220020006D00730064006100740061003A0072006F0077004F0072006400650072003D002200300022003E000D000A0020002000200020002000200020002000200020003C006E006500740077006F0072006B005F00690064003E0031003C002F006E006500740077006F0072006B005F00690064003E000D000A0020002000200020002000200020002000200020003C006E006500740077006F0072006B005F006E0061006D0065003E00780066006400660064006600640066003C002F006E006500740077006F0072006B005F006E0061006D0065003E000D000A00200020002000200020002000200020003C002F0044006100740061003E000D000A002000200020002000200020003C002F0044006F00630075006D0065006E00740045006C0065006D0065006E0074003E000D000A0020002000200020003C002F006400690066006600670072003A0064006900660066006700720061006D003E000D000A00200020003C002F0044006100740061003E000D000A00200020003C004F007000650072006100740069006F006E00540069006D0065003E0032003000300037002D00310031002D00320036005400310034003A00350038003A00310030002E0030003900380036003100350035005A003C002F004F007000650072006100740069006F006E00540069006D0065003E000D000A00200020003C0042006C006F00620043006F006C0075006D006E004E0061006D006500730020002F003E000D000A00200020003C0042006C006F00620047005500490044003E00630061006200610037006500660039002D0066003500650065002D0034006200370035002D0061006400360030002D003000390063003500330038003500640031003100310031003C002F0042006C006F00620047005500490044003E000D000A003C002F0045006E00740065007200700072006900730065004100750064006900740044006100740061003E00 Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.
F620FA2D-309C-DC11-8EED-0002B3D9F7B5 //TyMetrix360Audit/DataWriter 386DDD04-7E55-466A-BE83-37EFC20910B9 tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender //TyMetrix360Audit/Contract 2007-11-26 14:59:26.813 0 //TyMetrix360Audit/Message 0 0 0x3C003F0078006D006C002000760065007200730069006F006E003D00220031002E0030002200200065006E0063006F00640069006E0067003D0022007500740066002D003100360022003F003E000D000A003C0045006E0074006500720070007200690073006500410075006400690074004400610074006100200078006D006C006E0073003A007800730069003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002D0069006E007300740061006E00630065002200200078006D006C006E0073003A007800730064003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D00610022003E000D000A00200020003C005400610062006C0065004E0061006D0065003E004E004500540057004F0052004B003C002F005400610062006C0065004E0061006D0065003E000D000A00200020003C005000720069006D0061007200790043006F006C0075006D006E004E0061006D0065003E006E006500740077006F0072006B005F00690064003C002F005000720069006D0061007200790043006F006C0075006D006E004E0061006D0065003E000D000A00200020003C004F007000650072006100740069006F006E003E0055003C002F004F007000650072006100740069006F006E003E000D000A00200020003C004400620055007300650072003E00640062006F003C002F004400620055007300650072003E000D000A00200020003C004400610074006100620061007300650020002F003E000D000A00200020003C0044006100740061003E000D000A0020002000200020003C00780073003A0073006300680065006D0061002000690064003D0022004E006500770044006100740061005300650074002200200078006D006C006E0073003D0022002200200078006D006C006E0073003A00780073003D00220068007400740070003A002F002F007700770077002E00770033002E006F00720067002F0032003000300031002F0058004D004C0053006300680065006D0061002200200078006D006C006E0073003A006D00730064006100740061003D002200750072006E003A0073006300680065006D00610073002D006D006900630072006F0073006F00660074002D0063006F006D003A0078006D006C002D006D007300640061007400610022003E000D000A002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D0022004E00650077004400610074006100530065007400220020006D00730064006100740061003A004900730044006100740061005300650074003D0022007400720075006500220020006D00730064006100740061003A004D00610069006E0044006100740061005400610062006C0065003D0022004400610074006100220020006D00730064006100740061003A00550073006500430075007200720065006E0074004C006F00630061006C0065003D002200740072007500650022003E000D000A00200020002000200020002000200020003C00780073003A0063006F006D0070006C006500780054007900700065003E000D000A0020002000200020002000200020002000200020003C00780073003A00630068006F0069006300650020006D0069006E004F00630063007500720073003D0022003000220020006D00610078004F00630063007500720073003D00220075006E0062006F0075006E0064006500640022003E000D000A002000200020002000200020002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D002200440061007400610022003E000D000A00200020002000200020002000200020002000200020002000200020003C00780073003A0063006F006D0070006C006500780054007900700065003E000D000A0020002000200020002000200020002000200020002000200020002000200020003C00780073003A00730065007100750065006E00630065003E000D000A002000200020002000200020002000200020002000200020002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D0022006E006500740077006F0072006B005F00690064002200200074007900700065003D002200780073003A0069006E007400220020006D0069006E004F00630063007500720073003D0022003000220020002F003E000D000A002000200020002000200020002000200020002000200020002000200020002000200020003C00780073003A0065006C0065006D0065006E00740020006E0061006D0065003D0022006E006500740077006F0072006B005F006E0061006D0065002200200074007900700065003D002200780073003A0073007400720069006E006700220020006D0069006E004F00630063007500720073003D0022003000220020002F003E000D000A0020002000200020002000200020002000200020002000200020002000200020003C002F00780073003A00730065007100750065006E00630065003E000D000A00200020002000200020002000200020002000200020002000200020003C002F00780073003A0063006F006D0070006C006500780054007900700065003E000D000A002000200020002000200020002000200020002000200020003C002F00780073003A0065006C0065006D0065006E0074003E000D000A0020002000200020002000200020002000200020003C002F00780073003A00630068006F006900630065003E000D000A00200020002000200020002000200020003C002F00780073003A0063006F006D0070006C006500780054007900700065003E000D000A002000200020002000200020003C002F00780073003A0065006C0065006D0065006E0074003E000D000A0020002000200020003C002F00780073003A0073006300680065006D0061003E000D000A0020002000200020003C006400690066006600670072003A0064006900660066006700720061006D00200078006D006C006E0073003A006D00730064006100740061003D002200750072006E003A0073006300680065006D00610073002D006D006900630072006F0073006F00660074002D0063006F006D003A0078006D006C002D006D00730064006100740061002200200078006D006C006E0073003A006400690066006600670072003D002200750072006E003A0073006300680065006D00610073002D006D006900630072006F0073006F00660074002D0063006F006D003A0078006D006C002D0064006900660066006700720061006D002D007600310022003E000D000A002000200020002000200020003C0044006F00630075006D0065006E00740045006C0065006D0065006E0074003E000D000A00200020002000200020002000200020003C00440061007400610020006400690066006600670072003A00690064003D00220044006100740061003100220020006D00730064006100740061003A0072006F0077004F0072006400650072003D002200300022003E000D000A0020002000200020002000200020002000200020003C006E006500740077006F0072006B005F00690064003E0031003C002F006E006500740077006F0072006B005F00690064003E000D000A0020002000200020002000200020002000200020003C006E006500740077006F0072006B005F006E0061006D0065003E00780066006400660064006600640066003C002F006E006500740077006F0072006B005F006E0061006D0065003E000D000A00200020002000200020002000200020003C002F0044006100740061003E000D000A002000200020002000200020003C002F0044006F00630075006D0065006E00740045006C0065006D0065006E0074003E000D000A0020002000200020003C002F006400690066006600670072003A0064006900660066006700720061006D003E000D000A00200020003C002F0044006100740061003E000D000A00200020003C004F007000650072006100740069006F006E00540069006D0065003E0032003000300037002D00310031002D00320036005400310034003A00350039003A00320036002E0037003800340036003400330031005A003C002F004F007000650072006100740069006F006E00540069006D0065003E000D000A00200020003C0042006C006F00620043006F006C0075006D006E004E0061006D006500730020002F003E000D000A00200020003C0042006C006F00620047005500490044003E00350034006300380036006200330036002D0061006300330066002D0034006600300034002D0062006600660066002D003400310062003800310065003500360035006500360066003C002F0042006C006F00620047005500490044003E000D000A003C002F0045006E00740065007200700072006900730065004100750064006900740044006100740061003E00 Service Broker received an error message on this conversation. Service Broker will not transmit the message; it will be held until the application ends the conversation.
The main point is that the sys.transmission queue retains the messages with the error 'Service Broker will not transmit the message; it will be held until the application ends the conversation.'
4. I execute the following sql statment on the sender:
select * from sys.conversation_endpoints
and see that the conversation that sent the messages are in state 'ER'
5. I then recreate the receiver with the following script:
create service [//TyMetrix360Audit/DataWriter] authorization dbo on queue dbo.TyMetrix360AuditQueue([//TyMetrix360Audit/Contract])
6. I send some more messages and see that the new messages are being received and processed correctly.
7. But what of the messages stuck in the sender transmission queue. How are these messages to be resent? Since the conversations are in state 'ER' it seems they are not being resent. Do I need to write a custom SQL script to resend them? I do not want to end the conversation because the message will be lost and not resent.
So, in conclusion, the main question is:
When the receiver goes down, or the receiver service broker simply does not exist and messages pile up the sender transmission queue like in my example how do these messages get resent when the receiver is restored?
By the way when I run
select service_broker_guid from sys.databases where database_id = db_id()
I get the same GUID after the server is restored.
Thanks
Here is a description:
1. drop receiver side service broker with sql command : drop service [//TyMetrix360Audit/DataWriter]
2. send a message from the sender.
3. now the sys.transmission_queue on the sender keeps the message.
The relevant tables on the sender and receiver no look like this:
the following summarizes the transmission queue on the SENDER:
conversation handle message_body transmission status
5A0F1D1F-449C-DC11-8EED-0002B3D9F7B5 my message in binary, ie 0x.F4E1.... <blank>
the following summarizes the sys.conversation_endpoints on the SENDER:
conversation handle conversation_id state
5A0F1D1F-449C-DC11-8EED-0002B3D9F7B5 461891C8-5D53-4D89-A7C6-097FE2EDB22A CO
the following summarizes the transmission queue on the RECEIVER:
conversation handle message_body transmission status
5A0F1D1F-449C-DC11-8EED-0002B3D9F7B5 message body of error message...... One or more messages could not be delivered to the local service targeted by this dialog.
the following summarizes the sys.conversation_endpoints on the RECEIVER:
conversation handle conversation_id state
5A0F1D1F-449C-DC11-8EED-0002B3D9F7B5 461891C8-5D53-4D89-A7C6-097FE2EDB22A DO
What do I do now?
There is a message stuck in the sender transmission queue. You responded in my last post that I need to do a RECIEVE on the sender and end the conversation myself? This is not helping me. I am still confused about the answer to this. Can you provide some sort of code outline or steps to resolve this issue. As of now I do not know how to resend my messages stuck in the sender transmission queue and they can not be lost when the conversation is ended.
Also, I thought SQL Service broker was supposed to hadle things like this. It is common for the receiver to not be there. In this case the messages should resend automatically once the receiver is back up. Please help as there is no documentation online about how to resolve this issue and your last response was not adequate.
Thanks
Scenario: (1) Initiator/Target are running on different machines; (2) Target is on SQL Standard Edition with service pack 1; (3) Initiator ends the conversation.
If Initiator is running on SQL Express Edition with service pack 1 or 2, Send message? and End conversion? will introduce two messages in initiators sys.transmission_queue. One is empty message with correct message type; the other is empty message with no message type. Both of them are from the Target to the Initiator.
If Initiator is running on SQL Standard Edition with service pack 1, with same stored procedure (attached below), there is no message left in initiators sys.transmission_queue.
We like to find the way to make Initiator on SQL Express Edition acting same as it on SQL Standard Edition, namely not left messages in the initiators sys.transmission_queue
CREATE PROCEDURE [dbo].[CreateMessageForQueue]
@message varchar(max)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @handle UNIQUEIDENTIFIER;
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE [TCP://Initiator:4321]
TO SERVICE N'TCP://Target:4567'
ON CONTRACT LogContract
WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @handle
MESSAGE TYPE LogMessage(@message);
END CONVERSATION @handle
END
Thanks,
Hello,
This is info that I am still not certain about and I just need to make sure, my gut feeling is correct:
A.
When a procedure is triggered upon reception of a message in a queue, what happens when the procedure fails and rolls back?
1. Message is left on the Queue.
2. is the worker procedure triggered again for the same message by the queue?
3. I am hoping the Queue keeps on triggering workers until it is empty.
My scenario is that my queue reader procedure only reads one message at a time, thus I do not loop to receive many messages.
B.
For my scenario messages are independent and ordering does not matter.
Thus I want to ensure my Queue reader procedures execute simultaneously. Is reading the Top message in one reader somehow blocking the queue for any other reader procedures? I.e. if I have BEGIN TRANSACTION when reading messages of the Queue, is that effectively going prevent many reader procedures working simultaneously. Again, I want to ensure that Service broker is effectively spawning procedures that work simultaneously.
Thank you very much for the time,
Lubomir
I am using SQL2005 merge replication and have pull subscribers on a low bandwidth link
I am compressing the snapshot into an alternate folder. Files are not put into the default folder
When I start a synchronization, I would expect the cab file to be copied to the subscriber and then the files to be extracted locally at the subscriber in order to apply the snapshot
However, what appears to be happening is that the files are being extracted from the cab file on the publisher (in a UNC specified directory) and then copied in their uncompressed form to the subscriber - resulting in an extremely slow snapshot application.
Any ideas what I am doing wrong? I have read about the options for using FTP to transfer snapshot files, but I am not clear whether I have to use FTP in order to transmit a compressed snapshot. I don't want to use FTP unless I need to.
Any suggestions would be much appreciated
aero1
I have an issue in my production , SQL server 2008 R2 , in Error log it is said “ An error occurred in dialog transmission Error : 9655 , State : 3 and Error : 9736 Severity : 16 State 0
I checked in sysmessages , it is said :” The transmission queue table structure in database is inconsistent. Possible database corruption “
So do you think I need to run DBCC checkdb ? Should I run it after working hours or it is fine within working hours ?
I have a requirement where once we create a new record in a table, we submit a query to fetch some data and save it in one of the columns of the newly created record. The main requirement is that the server where we fetch the data from can be down for sometime for regular maintenance and we do not want to loose the fetch query in that process. Is there a way we can implement this?
Thanks.
I have a table that I want to act as a queue.
It has no indexes and no key. Just one column.
Basically I want a stored procedure that will pull / return the first record off the queue (table) and delete it. I'd rather not use MSMQ for this.
There will be about 10 users trying to do this at the same time and will be trying to pull of about 15 times every second.
How can I do this and ensure that no two requests pull off the same row?
Thanks,
Kevin
Hi Folks,
I was testing my error handling and purposefully failed some messages. Automatic posion message detection kicked in and disabled my queue. I tried the following, one at a time to enable it again but it doesn't work:
ALTER QUEUE MigrationQueue WITH STATUS = ON;
ALTER QUEUE MigrationQueue WITH STATUS = ON, ACTIVATION (STATUS = ON);
I would have thought the first line would've worked but I get the following when trying to receive...
The service queue "MigrationQueue" is currently disabled.
Help.
Hello!
I am running a basic SSB queue setup (more or less the Hello World example)and running into the following error message:
Transaction (Process ID 120) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
At first, I thought it was because I had the Initiator and Target Services on the same Queue, but I get this error even when I separate the two Services onto two Queues. This happens when I run more than one Target application receiving messages from the Target Queue.
Does anybody have any idea what could be happening here? Am I not allowed to set up more than one receiver?
Thanks --
Robert
I have a queue that, after running fine for several days will mysteriously turn off. It doesn't seem to be related to a poison message because I can restart the queue and processing resumes just fine. What are all the scenarios that would cause a queue to turn itself off, so I can 1) take preemptive action to prevent it from happening in the first place and 2) respond appropriately when it occurs.
Also, how to properly setup and verify that the BROKER_QUEUE_DISABLED is working properly. This is the SQL that I have so far, but is there a more direct way to raise the event other than writing an activated stored procedure that rolls back 5 times?
CREATE QUEUE [EventNotificationsQueue];
GO
CREATE SERVICE [EventNotificationsService]
ON QUEUE [EventNotificationsQueue]
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
CREATE EVENT NOTIFICATION [QueueDisabled]
ON QUEUE [MyQueue]
FOR BROKER_QUEUE_DISABLED
TO SERVICE 'EventNotificationsService', 'MyDatabase';
GO
HI There
My activated proc is rolling back the transaction and putting the message abck on the queue infinately ?
Normally it disabled the queue after a few rollbacks, i can see in the sql log that it just keeps rolling back and re-activating thousands of times.
It only stops when i disable activation on the queue.
WHy is the queue not disabling ?
Thanx
Running transactional replication, dedicated server for distributor. While performance in terms of latency is excellent (usually 1 sec, almost never higher than 4) the disk queue length on the distributor is extremely high (over 6 usually). Is this typical? On any other server I would be very concerned, but cpu and memory usage are excellent and as said, latency is good. what is recommended config for distributor? others see high queue length?
View 2 Replies View RelatedHi,
I have created Queue with the following syntax. But it is not getting activated itself. What I have to do to get it activated itself, and what could be the frequency by default.
CREATE QUEUE NewCustomerQueue
WITH ACTIVATION
(PROCEDURE_NAME = prProcessNewCustomers,
STATUS = ON,
MAX_QUEUE_READERS = 1,
EXECUTE AS SELF)
GO
If I execute the prProcessNewCustomers procedure manually it is showing that the Queue has been activated. What change I have to make in the syntax to get it activated itself.
Actually I have two scenarios in my requirement,
1. One Queue processing immediately when it receives data (Order Processing)
2. Another Queue, Process when the server is idle i.e., off-peak time (for mailing)
What syntax I have to use for these.
Please help.
Thanks in advance
Babu
Howdy,
This is a follow on from a previous post
http://www.dbforums.com/t984271.html
And now I have found something interesting :
(1) When I was monitoring the SystemProcessor Queue locally ( Via a term server login onto the box ) I would see a queue of 3-4. If I monitor the same parameter from a remote PC, I see a Processor Queue of 1 - why?
The box had 1 GB RAM ( SQL used 500 MB and had 250 MB free according to Task manager ).
(2)
I have another almost identical box that has same CPU but twice ammount of RAM ( 2 GB ) but has SystemProcessor Queue of almost
0 - why?
All other parameters for Disk, IO etc are fine.
Cheers,
SG
avg disk queue length is 100%. Any ideas?
=============================
http://www.sqlserverstudy.com
Again one client with SAN EMC and again performace is several times worse then you can have with a cheap and primitive IDE drive... :(
Anyway, my question.
I am monitoring many parameters, including Avg Read Queue and Avg Write Queue.
So if I ReadQueue=3 and WriteQueue=7, what does it mean?
Scenario 1, there are 2 different Queues (R- read request, W - write request):
Windows --> Device
Read. Queue: R:R:R
Write Queue: W:W:W:W:W:W:W
Scenario 2:
Windows --> Device
Common Queue: W.R.W.W.R.W.W.R.W.W
In other words, if SQL server flushes writes (TRAN COMMIT or CHECKPOINT), generating hundreds or even thousands of write requests in few milliseconds, so Queue grows to 100-300 for a second or so, are read requests locked during that time?
I am trying to implement a very fast queue using SQL Server.The queue table will contain tens of millions of records.The problem I have is the more records completed, the the slower itgets. I don't want to remove data from the queue because I use thesame table to store results. The queue handles concurrent requests.The status field will contain the following values:0 = Waiting1 = Started2 = FinishedAny help would be greatly appreciated.Here is a simplified script to demonstrate what has been done.CREATE TABLE [dbo].[Queue] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[JobID] [int] NOT NULL ,[Status] [tinyint] NOT NULL) ON [PRIMARY]GOCREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]GOCREATE PROCEDURE dbo.NextItem@JobID integer,@ID integer outputASSELECT TOP 1 @ID = [ID]FROM Queue WITH (READPAST, XLOCK)WHERE (Status = 0) AND (JobID = @JobID)RETURNGO
View 6 Replies View Related
Hi,
I created several queues on the default filegroup and I was wondering how can I change a queue filegroup?
Thanks,
Shai.
I am doing backup job using windows 2003 backup. I hv several scheduled backup job. And sometimes, when the first backup job not yet finish backing up, the second backup job will automatic starts at the set time resulting 2 backup jobs at 1 time. I need to know whether in SQL2000/2005, we can set the tsql command to do the job queue for the windows 2003 backup job, meaning if the 1st backup job is still ongoing, the 2nd backup job will hv to wait even though the set time for the job was already expired. When the 1st job done, then only the 2nd job started.
Appreciate the feedback..
tq
hi everybody,
i am working on a SSIS package that creates Multiple file from a table depending on a particular field.
now the requirement from the target system is i have send the files directly to the particular serverrs.
for that i have to post my files in msmq and then use some other technology to achieve this.
if anybody has some ideas on this it will be very helpful for me.
thanks in advance
srikanth
Hello,
I'm currently looking for a way to decently pauze the processing of messages by activation procedures. When I pauze the processing I want to be sure that the running activation procedures terminated.
I thought I could do this using ALTER QUEUE WITH ACTIVATION (STATUS = OFF) and sys.dm_broker_activated_tasks.
This is what I've tested: I set up one queue with an activation procedure attached to it that receives one message, then waits for one minute, then writes that message into a table. The queues max queue readers is set to 2. Now, I put 5 messages in the queue, 2 procs pop up in sys.dm_broker_activated_tasks. I run the alter queue statement shown above and check sys.dm_broker_activated_tasks again. It is empty so allegedly there are no tasks running. I check the table in which the messages are written by the activation procs and after some time I can see the results of both activation procedures which were according to sys.dm_broker_activated_tasks not running (but of course they were). The rest of the process works as expected, the procs reactivate upon reenabling the activation status and all is well.
Is there another way of checking the running activation procedures that checking sys.dm_broker_activated_tasks?
Is this intended behaviour or just a bug?
Hi There
I am having alot of trouble with the order in which messages are being delivered, here is my scenario.
I have a transaction action table :
message 1 - xml schema A.
message 2 - xml schema A.
message 3 - xml schema B.
message 4 - xml schema D.
I have the following SP:
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
AT the target i get message 1 in the queue first them message 2. But then i try this.
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 3
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 4
COMMIT
At the target i get message 4 first it has the lower queuing_order on the queue, somehow it got ont he queue first.
I have tried turning validation off on the mssage types as i thought it may have something to do with the xml, but same problem.
I then tried to do a commit after each message like this:
BEGIN TRAN
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
COMMIT
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
COMMIT
But message 4 still gets on the queue first . WTF is going on ?
The only way i can get this to work is like this.
BEGIN TRAN
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 1
WAITFOR DELAY '00:00:01'
BEGIN DIALOG CONVERSATION
SEND ON CONVERSATION message 2
COMMIT
Why does this work ? and why do i not have this issue with message 1 and 2? If it has soemthing to do with the xml what is it?
Please help.
I know we are not allowed to benchmark SQL Server but..... It would be nice to have material to present which demonstrates the performance gains using a queue compared to insert/delete in a SQL table.
Logically it seems faster to use a queue due to the conversation grouping locking and the service broker itself. But there seems to be some overhead involved just to manage these queues that the service broker has to perform.
I am sure we are not unique with the choice to figure out if we will get a boost in performance using SQL a queue between services rather than a table to queue data. What is available to help understand the performance gains of using a queue?
I am running SQL server 2000 SP4 on a server with 2 Dual core 4G processors with data attached via a SAN>
I have a 70G database with 10 users that is giving attrocious performance. I have just tried to run a count(*) accross a couple of tables and am still waiting for the results 15 mins later. When I look at the disk queue it is around 50/60. I thought the target for this was around 2. I am sure that the hardware that we have in place is capable of running this db. However I`m not sure how to fully analyse what is going wrong here.
Any tips would be greatfully received.
Si