April 7, 2017 at 8:33 am
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
April 7, 2017 at 10:43 am
luismarinaray - Friday, April 7, 2017 8:33 AMDear 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
There are multiple things wrong with this session you have. First, the event doesn't exist, I am looking for similar events in 2008R2 but nothing related to the blocked process report. Second is the target is not a legitimate target. event_file was introduced in 2012. The third thing is a file target in 2008 requires the xem file be defined as well. You will want to look for the asynchronous_file_target in 2008 and 2008 R2.
Now on to semantics. Are you looking for blocking or are you just looking for locks? Locks would be rather heavy handed to monitor where blocking may make more sense.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2017 at 11:51 am
Hi,
Thanks for your response, really i am looking for blocking.
I am wondering. How could I setup an extended event messaging report. The piece of code I take it from an example, that is available in
http://www.sqlskills.com/blogs/erin/capture-blocking-information-with-extended-events-and-the-blocked-process-report/
but does not work in SQL Server 2008 R2, I am looking for help if it is possible, where could I begin to loook ?
Thanks again
April 7, 2017 at 12:04 pm
I forgot to add that there is another great resource on Extended Events.
There is loads of information here:
http://bit.ly/XE60Days
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2017 at 12:07 pm
luismarinaray - Friday, April 7, 2017 11:51 AMHi,
Thanks for your response, really i am looking for blocking.
I am wondering. How could I setup an extended event messaging report. The piece of code I take it from an example, that is available in
http://www.sqlskills.com/blogs/erin/capture-blocking-information-with-extended-events-and-the-blocked-process-report/
but does not work in SQL Server 2008 R2, I am looking for help if it is possible, where could I begin to loook ?
Thanks again
Weird, my initial response to this particular question did not post.
In order to capture the blocked report process, you will have to use another mechanism in your old version of SQL Server (7+ years old now). This is unforunately one of the rare cases where a server side trace wins out over XE. You can use profiler or a server side trace to capture it.
Here is another resource if you go the server side trace route:
http://michaeljswart.com/2011/05/when-to-use-blocked-processes-reports/
My recommendation would be to upgrade from this really old version of SQL Server. Get onto one of the more recent editions where so much has been improved in so many areas including the area of Extended Events.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 7, 2017 at 2:25 pm
luismarinaray - Friday, April 7, 2017 11:51 AMHi,
Thanks for your response, really i am looking for blocking.
I am wondering. How could I setup an extended event messaging report. The piece of code I take it from an example, that is available in
http://www.sqlskills.com/blogs/erin/capture-blocking-information-with-extended-events-and-the-blocked-process-report/
but does not work in SQL Server 2008 R2, I am looking for help if it is possible, where could I begin to loook ?
Thanks again
Since you're using 2008 R2, a feature that may work better in this case is Event Notifications:
https://www.sswug.org/basitaalishanmasood-al-farooq/sql-server/monitoring-blocked-processes-with-event-notifications/
I've had a similar process as the sample here running on a 2008 server for the past 6 years. (I just updated that server to 2014 last year)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply