Extended Events locks

Question

Extended Events locks

Lou

SSCrazy

Points: 2929
More actions
April 6, 2017 at 1:48 pm

#329119

Dear colleagues,
I am setting up extended events in a SQL Server 2008R2 SP3 with an interests in getting information about locks in an OLTP database, in this version we have not availability of the blockingProcessReport, my question is how or whats metrics could I use in defining an event to capture this info ?.
Ideas are welcome
Thanks again
This code is not valid in SQL Server 2008R2
CREATE EVENT SESSION [Capture_BlockedProcessReport] ON SERVER
ADD EVENT sqlserver.blocked_process_report ADD TARGET package0.event_file(
SET filename=N'C:\Temp\Capture_BlockedProcessReport.xel')
WITH (MAX_MEMORY=8192 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF);
GO
And Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

I don't believe you can get blocked process report information with extended events on SQL Server 2008. You'd likely need to look at using event notifications instead.
Try these links for a couple of examples on how to do this:
EVENT NOTIFICATIONS: Monitoring blocked processes (and other events)
Monitoring Blocked Processes with Event Notifications

Sue

Lou SSCrazy Points: 2929 More actions · Answer 2

Finally,
I am getting a report, using, SQL Server Performance Dashboard 2012, when you get Current Waiting Requests and hit lock, you get a blocking report.
Now, I am looking, how to send it via email, without using reporting services and when certain conditions apply.
Thanks to all for your responses