May 20, 2010 at 4:01 am
Hi,
What events do I need to include in profiler to trace SQL batches that fail execution.
The Error column for the SQL:BatchCompleted event, contains "0 - OK" when I run the following batch that fails in SSMS.
select 1/0;
go
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
thx
jt
May 20, 2010 at 6:39 am
What's the purpose of the trace? Do you just want to see that they started? Then I'd use the RPC or Batch Start event. If you're trying to see specifically which statement failed, you'll need to capture statement completion, but that's very resource intensive, so you need to be very careful when capturing it to prevent the trace from affecting the monitored server. Or do you have something else in mind?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2010 at 9:30 am
I'm trying to monitor the server when I know queries are failing.
I know my batch is failing with an error, there is a SQL:BatchCompleted event, but it does not show an error in the Error column.
It seems the presence of the SQL:BatchCompleted event doesn't mean it completed successfully.
May 20, 2010 at 10:51 am
It really depends on what the error is. Not all errors cause a loss of connection, so you'll still see the RPC or Batch completion event.
Profiler isn't necessarily the place to gather this information, but from the sounds of things, I'd suggest getting statement completions. YOu'll at least know which statements within the misbehaving procs were completed. That will narrow down the troubleshooting.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 21, 2010 at 2:47 am
thx for you replies Grant, I would have thought profiler would help me identify the reason by batches are failing.
How about the User Error Messages event under the Error and Warning category? or maybe the Exception event.
May 21, 2010 at 4:36 am
I've honestly never tried it that way, so I'm not sure where to go. If you know which procedure is giving your problems, I just work with it on my local machine against a copy of the database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply