Logging SQLExceptions
-
We have a web front-end which I believe in some cases is throwing unhandled SQLExceptions, like Conversion errors, or Foreign key constraint errors, etc.
I realize the best approach is probably for the developers to place their code in TRY/CATCH to handle these errors, but in the meantime, I was wondering if there is anything from a SQL side I can implement to see/log these errors/Exceptions?
Anything from a SQL Trace or Extended Events that I can use?
Thank you
-
Extended Events is the way to go.
Have a look at the attached script ( capturing key violations ) to get you started
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thank you
-
Yeah, you can set up SQL Trace or Extended Events to catch those errors. It's like a net for those SQLExceptions. But in the long run, asking devs to handle errors in TRY/CATCH is the best bet. It keeps things tidy!
- Bedeencion40 wrote:
Yeah, you can set up SQL Trace or Extended Events to catch those errors. It's like a net for those SQLExceptions. But in the long run, asking devs to handle errors in TRY/CATCH is the best bet. It keeps things tidy!
Friends don't let friends use Trace on modern systems. No need to more negatively impact your machine than you have to, and Extended Events is radically more lightweight than Trace for the vast majority of what they can both capture. Then, XE captures a lot more besides.
Just sayin'.
100% agreement on the devs.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:Bedeencion40 wrote:
Yeah, you can set up SQL Trace or Extended Events to catch those errors. It's like a net for those SQLExceptions. But in the long run, asking devs to handle errors in TRY/CATCH is the best bet. It keeps things tidy!
Friends don't let friends use Trace on modern systems. No need to more negatively impact your machine than you have to, and Extended Events is radically more lightweight than Trace for the vast majority of what they can both capture. Then, XE captures a lot more besides.
Just sayin'.
100% agreement on the devs.
While I agree that there are some added benefits to EE, I've found that people that bring SQL Server to it's knees just like they didd with SQL Profiler simply because they don't get it. I do currently use EE to capture the actual code that caused any growth in TempDB (really helpful, BTW).
Have they made it any easier to read the files through T-SQL that EE stores or is it still XML??? And, no... conversion to JSON would be equally stupid (IMHO... sorry, had to use that word here for this). Have they made the GUI interface so that you can see code as it's formatted rather than a few tens of characters on a single line?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Grant Fritchey wrote:Bedeencion40 wrote:
Yeah, you can set up SQL Trace or Extended Events to catch those errors. It's like a net for those SQLExceptions. But in the long run, asking devs to handle errors in TRY/CATCH is the best bet. It keeps things tidy!
Friends don't let friends use Trace on modern systems. No need to more negatively impact your machine than you have to, and Extended Events is radically more lightweight than Trace for the vast majority of what they can both capture. Then, XE captures a lot more besides.
Just sayin'.
100% agreement on the devs.
While I agree that there are some added benefits to EE, I've found that people that bring SQL Server to it's knees just like they didd with SQL Profiler simply because they don't get it. I do currently use EE to capture the actual code that caused any growth in TempDB (really helpful, BTW).
Have they made it any easier to read the files through T-SQL that EE stores or is it still XML??? And, no... conversion to JSON would be equally stupid (IMHO... sorry, had to use that word here for this). Have they made the GUI interface so that you can see code as it's formatted rather than a few tens of characters on a single line?
Oh, you know the answer. It's still XML. Of course it is. I'm not defending it. It's XML. Can you query it? Yes. There's an extended procedure, but then, it's XML. However, if you do want to avoid the XML, then you can use DBATools through Powershell which will get you out of dealing with the XML in any manner and run queries against the data collected.
As I said, I won't attempt to defend the XML because, ew, XML. It is indefensible. However, the XML is 100% no reason to throw the baby out with the bathwater. Is it a pain? Yes. Is that pain either or both, avoidable or offset? Equally, yes.
As to the GUI, one, they are working on it again (finally).
Still better than than Trace. Still safer for your systems than Trace.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Oh, and I missed the formatting thing. Yeah, it does it. Just double click on the batch_text or whatever field and it will open a window like this one:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning - Grant Fritchey wrote:Jeff Moden wrote:Grant Fritchey wrote:Bedeencion40 wrote:
Yeah, you can set up SQL Trace or Extended Events to catch those errors. It's like a net for those SQLExceptions. But in the long run, asking devs to handle errors in TRY/CATCH is the best bet. It keeps things tidy!
Friends don't let friends use Trace on modern systems. No need to more negatively impact your machine than you have to, and Extended Events is radically more lightweight than Trace for the vast majority of what they can both capture. Then, XE captures a lot more besides.
Just sayin'.
100% agreement on the devs.
While I agree that there are some added benefits to EE, I've found that people that bring SQL Server to it's knees just like they didd with SQL Profiler simply because they don't get it. I do currently use EE to capture the actual code that caused any growth in TempDB (really helpful, BTW).
Have they made it any easier to read the files through T-SQL that EE stores or is it still XML??? And, no... conversion to JSON would be equally stupid (IMHO... sorry, had to use that word here for this). Have they made the GUI interface so that you can see code as it's formatted rather than a few tens of characters on a single line?
Oh, you know the answer. It's still XML. Of course it is. I'm not defending it. It's XML. Can you query it? Yes. There's an extended procedure, but then, it's XML. However, if you do want to avoid the XML, then you can use DBATools through Powershell which will get you out of dealing with the XML in any manner and run queries against the data collected.
As I said, I won't attempt to defend the XML because, ew, XML. It is indefensible. However, the XML is 100% no reason to throw the baby out with the bathwater. Is it a pain? Yes. Is that pain either or both, avoidable or offset? Equally, yes.
As to the GUI, one, they are working on it again (finally).
Still better than than Trace. Still safer for your systems than Trace.
To be sure, I've not "thrown the baby out". Heh... I just wish it were easier to get the baby out of the bath water. 😀
Like I said, I've recently used EE for the TempDB growth (and it records "shrinks" as well) and the end result is great. I don't need to tell you that getting to the end result was a pain. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Grant Fritchey wrote:
Oh, and I missed the formatting thing. Yeah, it does it. Just double click on the batch_text or whatever field and it will open a window like this one:
Yes. That works but, again, another "click" required during online analysis, which is normally done when you're having a problem and that's a part of my original gripe. The "improvements" don't include some of the old features that were very, very handy.
This reminds me of the 2016 release of STRING_SPLIT(). 😀
Grant Fritchey wrote:Still better than than Trace. Still safer for your systems than Trace.
I'm assuming that, by the word "Trace", you also mean "SQL Profiler"...
I have to disagree with your definitions of "better" and "safer" above. I never had an issue when I used SQL Profiler "correctly" (which many people don't know how to do). EE does NOT prevent people that make the same mistakes as they did in SQL Profiler. And the idea of having to use yet another tool (DBATOOLS, etc) to extract info from an MS product tells me that MS sucks when it comes to such things.
Again, this reminds me of things like the 2016 release STRING_SPLIT() and the fact that it took them almost 7 years to fix that because they're not actually in touch with what real people need to do on a daily basis.
Don't get me started on the new GENERATE_SERIES() function. 😀 Useful but woefully incomplete compared to similarly named functions in other databases. Worse yet, it breaks "Minimal Logging", which is a horror story for people that do the things that I have to do. 🙁
Getting back to EE, good tool... but not great. Too many important tradeoffs compared to what I used to be able to do. I DO have to use it because of some of the "improvements", but lordy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply