June 17, 2014 at 8:58 am
Hey all,
I'm fairly new to extended events and I'm hoping there is a simple answer to my question.
Here is the problem:
I have an existing EE setup that captures all failing queries (see code below). The problem is that I also want to somehow capture RPC_starting so that I can see which parameters are passed in whenever a query fails. Is there a way to somehow capture those two events (error_reported & rpc_starting), but only capture rpc_starting when there is actually an error reported?
Or maybe just an event on rpc_starting and somehow filter to only capture when there is an error?
Any help is appreciated.
Existing error_reported EE code:
CREATE EVENT SESSION [what_queries_are_failing] ON SERVER ADD EVENT sqlserver.error_reported (
ACTION(sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.database_id, sqlserver.session_id,
package0.collect_system_time, sqlserver.transaction_id, sqlserver.username, sqlserver.client_hostname)
WHERE (
(
[severity] > (10)
AND [package0].[counter] <= (1000)
)
)
) ADD TARGET package0.asynchronous_file_target (
SET filename = 'D:\RDBMS\DBA_SCRIPTS\what_queries_are_failing.xel'
,metadatafile = 'D:\RDBMS\DBA_SCRIPTS\what_queries_are_failing.xem'
,max_file_size = 5
,max_rollover_files = 5
)
WITH (
MAX_MEMORY = 4096 KB
,EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS
,MAX_DISPATCH_LATENCY = 5 SECONDS
,MAX_EVENT_SIZE = 0 KB
,MEMORY_PARTITION_MODE = NONE
,TRACK_CAUSALITY = OFF
,STARTUP_STATE = OFF
)
ALTER EVENT SESSION [what_queries_are_failing] ON SERVER STATE = START
Edit: Changed rpc_completed to rpc_starting
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 17, 2014 at 9:20 am
RPC_Completed will only fire if there are no errors
Do you not need to put RPC_started?
Chris
June 17, 2014 at 9:57 am
You are correct. It should be RPC_Starting.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
June 17, 2014 at 11:00 am
This probably isn't ideal, but I think it could work. You could either add a second EE session or a second target to your existing EE session using the Pair Matching target. Then you'd have a starting event that doesn't have a corresponding completed event in the pair matching target whenever you have an error. Here's a blog post[/url] about using the Pair Matching target
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 17, 2014 at 2:19 pm
Thanks Jack. I might give that a try and let you know if it works.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
October 13, 2014 at 6:41 pm
and did it work? I have been trying to get it to work without success. (pair matching)
October 14, 2014 at 8:59 am
Unfortunately, I never gave the pair matching idea a try. Luckily, the developers found the parameters causing the failures in the application log so the EE wasn't needed anymore.
Twitter: @SQL_JGood
Blog: sqljgood.wordpress.com/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply