November 9, 2010 at 2:48 pm
Hi Everyone,
Just some quick background on my goal here. I have a 3rd party application and every so often one process will become locked up and will block pretty much every other database process for this one database/application (not a deadlock). What I am trying to do is to setup an Event Notification to become proactive so that if a process is blocked for x number of seconds that my team is notified and we can attack the issue before users start calling.
So here is my issue. I created the event notification and while I have been testing it by creating a process block or a deadlock the event notification is dropped and i get a series of errors in the server Event Viewer.
Here is my code, am I missing something obvious here? The process eventually bombs both when I used the stored procedure on the queue and when i dont use the stored procedure. My server is setup to raise the BLOCKED_PROCESS_REPORT after 10 seconds.
Thanks much.
-Dan
use MSDB
GO
--Create queue
Create Queue que_Deadlock_Notification
GO
--Create service with a contract based on the SQL PostEventNotification
Create Service svc_Deadlock_Notification
On Queue que_Deadlock_Notification
([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO
--Find the GUID of the current msdb database and then stop the query
Selectsb_guid = service_broker_guid
Fromsys.databases
Wherename = 'msdb'
--Create the event notification (update the guid with the msdb guid from the query above)
Create Event Notification evt_Deadlock_Notification
On Server
For DEADLOCK_GRAPH, BLOCKED_PROCESS_REPORT
To Service 'svc_Deadlock_Notification', 'DF8E3D7D-C7B5-48C2-92AF-2D82BC1C2B59'
--Create a deadlock history table
GO
Drop Table Deadlock_Notification_Tbl
GO
Create Table Deadlock_Notification_Tbl (
dl_id int identity (1, 1) Constraint PK_Deadlock_Notification Primary Key,
dl_dt datetime,
dl_detail xml,
conversation_hndl uniqueidentifier,
error_code varchar(2000)
)
GO
--Create deadlock Stored Proc
Create Procedure usp_Deadlock_Notification
As
--Return
Begin Try
Begin Transaction
Declare@conversation_handle uniqueidentifier, @msg_body xml, @msg_type_name nvarchar(128),
@dl_graph xml, @dl_dt datetime, @dl_id int
--Receive the message
WaitFor (
Receive Top (1)@conversation_handle = [conversation_handle],
@msg_body = convert(xml, message_body),
@msg_type_name = message_type_name
Fromque_Deadlock_Notification),
Timeout 1000
--Check to see if the message type is a deadlock msg
If @msg_type_name = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
and (@msg_body.exist('(EVENT_INSTANCE/TextData/deadlock-list)') = 1
or @msg_body.exist('(EVENT_INSTANCE/TextData/blocked-process-report)') = 1)
Begin
Select--@dl_graph = @msg_body.query ('(EVENT_INSTANCE/TextData/deadlock-list)'),
@dl_graph = @msg_body,
@dl_dt = @msg_body.value('(/EVENT_INSTANCE/PostTime)[1]','datetime')
Insert Into Deadlock_Notification_Tbl (dl_dt, dl_detail, conversation_hndl)
Values(@dl_dt, @dl_graph, @conversation_handle)
Select@dl_id = Scope_Identity()
--If Not Exists (Select 'x' From que_Deadlock_Notification Where [conversation_handle] = @conversation_handle) Begin
End Conversation @conversation_handle
--End
End
Commit Transaction
End Try
Begin Catch
Rollback Transaction
Exec Utilities.dbo.usp_GetErrorInfo @Error_Tbl = 'System_Tracking'
End Catch
GO
--Enable the queue
Alter Queue dbo.que_Deadlock_Notification
WithStatus = ON,
Activation (Procedure_Name = msdb.dbo.usp_Deadlock_Notification,
Status = ON,
Max_Queue_Readers = 100,
Execute As Owner)
GO
----------------------------------------------------------------------------------------------------
Samples to create a blocked process, execute each script in a different query window.
--Creates the block
Begin Tran
Updates
Setmname = 'J'
Frompc_Staff s
Wherelname = 'Smith'
Waitfor Delay '00:00:20'
Commit Tran
--Blocked script 1
Begin Tran
Updates
Setmname = 'J'
Frompc_Staff s
Wherelname = 'Smith'
Commit Tran
--Blocked script 2
Begin Tran
Updates
Setmname = 'J'
Frompc_Staff s
Wherelname = 'Smith'
Commit Tran
November 9, 2010 at 10:46 pm
It would help if you would post the exact error messages you are receiving. Hard to debug if we don't have that information.
November 10, 2010 at 7:10 am
You mean you aren't the know it all DBA that our employers expect us to be? 🙂
Here are the errors, i forgot to post them last night!
7 variations of this error (with different dialog handle ids).
Event notification conversation on dialog handle '{2A7AA028-3AEC-DF11-8FDE-001A4BE76FE2}' closed without an error.
1 error like this (DEADLOCK_GRAPH and BLOCKED_PROCESS_REPORT being interchanged based on what i am testing).
Failure to send an event notification instance of type 'DEADLOCK_GRAPH' on conversation handle '{397AA028-3AEC-DF11-8FDE-001A4BE76FE2}'. Error Code = '8429'.
And 1 error like this.
Event notification 'evt_Deadlock_Notification' in database 'master' dropped due to send time service broker errors. Check to ensure the conversation handle, service broker contract, and service specified in the event notification are active.
I receive the same errors each time i test it. Occasionally it saves the deadlock error or blocking error to my audit table, but inevitably it fails after i continue to test it out.
November 12, 2010 at 1:20 pm
I think i figured this out. I was ending the conversation each time an event was fired and because of this, it was causing the event notification to be dropped. However, now the conversations remain open even after the event notification has fired.
Is it ok to leave the conversations open or do they eventually close? Isn't the event notification supposed to send a message to the queue to close the conversation since the event notification open the conversation?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply