March 20, 2014 at 10:17 am
Hi all - I'm currently monitoring what is causing a sever to slow down.
I looked through the expensive queries and found the following; WAITFOR(RECEIVE TOP (1) message_type_name, conversation_handle, cast(message_body AS XML) as message_body from [SqlQueryNotificationService-1d215d45-2b9a-4313-9d00-d4793928c90b]), TIMEOUT @p2;
I understand that this is the SQL Queue but I would like to know the actual T-SQL that is being executed. Does anyone know how I can track this back.
Thanks!!!!
--------------------------------------------
Laughing in the face of contention...
March 20, 2014 at 1:05 pm
Just a guess,
It could be an activation stored procedure bound to that queue as part of a service broker configuration. The queue definition should point you to the SP for you to investigate.
Jim
March 21, 2014 at 2:51 am
Thanks for your response Jim!
Its defiantly not stored procedures as we don''t use them here. I can't work out why the transaction can't be viewed when being managed by the service broker. Gonna have to keep looking...
Thanks!
--------------------------------------------
Laughing in the face of contention...
March 21, 2014 at 7:00 am
arrjay (3/21/2014)
I can't work out why the transaction can't be viewed when being managed by the service broker. Gonna have to keep looking...Thanks!
I think I might be confused by what you are looking for. The WAITFOR...RECEIVE is the SQL statement. It's the command that dequeues messages. Generally the WAITFOR controls how often the queue gets checked for new messages along with a timeout value that should eventually cause an exit if there isn't traffic for awhile.
If you have no SPs in the database then it must be some connection that is running it.
Jim
March 21, 2014 at 7:31 am
OK. I think that clears it up! Thanks.
--------------------------------------------
Laughing in the face of contention...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply