Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Service Broker and Server Level Event Notification Issue Expand / Collapse
Author
Message
Posted Tuesday, November 9, 2010 2:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:37 AM
Points: 95, Visits: 243
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
Select sb_guid = service_broker_guid
From sys.databases
Where name = '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
From que_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
With Status = 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

Update s
Set mname = 'J'
From pc_Staff s
Where lname = 'Smith'

Waitfor Delay '00:00:20'

Commit Tran

--Blocked script 1
Begin Tran

Update s
Set mname = 'J'
From pc_Staff s
Where lname = 'Smith'

Commit Tran

--Blocked script 2
Begin Tran

Update s
Set mname = 'J'
From pc_Staff s
Where lname = 'Smith'

Commit Tran
Post #1018284
Posted Tuesday, November 9, 2010 10:46 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:33 AM
Points: 23,299, Visits: 32,048
It would help if you would post the exact error messages you are receiving. Hard to debug if we don't have that information.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1018378
Posted Wednesday, November 10, 2010 7:10 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:37 AM
Points: 95, Visits: 243
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.
Post #1018583
Posted Friday, November 12, 2010 1:20 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:37 AM
Points: 95, Visits: 243
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?
Post #1020183
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse