Looking for a possible SQL error

We're running a 3rd party ASP application with SQL 2008 Web edition. I suspect the application is failing to record some transactions and isn't reporting SQL errors, e.g., timeouts. I've confirmed with the vendor that the application does not report or log errors in any way.

I'd like to not have to instrument the application code and am looking for another solution to identify any SQL errors that may be happening.

Is there any such tool available or is this built into SSMS?

PS: I am a casual user of SQL these days but when pointed in the right direction, I usually get there.

Sam

I think you could trap those with SQL Profiler - which can "spy" on all SQL statements sent to the server. There might also be some system views that will yield that information, which would be easier to report on (compared to using SQL Profiler), but SQL Profiler would get you going in a simple fashion - the difficulty might be separating the Wheat from the Chaff.

I'd give the vendor a good kicking too - how are they ever going to provide support and diagnose a problem if they don't have any means of diagnosing it?

Timeouts often show a duration right around 30 seconds. We often run a trace to capture durations higher than 25 seconds.

Have a look at https://github.com/billgraziano/sql-scripts/tree/master/Extended%20Events

I use that to capture and report on SQL errors that applications don't log or report. It's been very helpful for me.

Hi Graz,

Can those two scripts be run without modification? It looks like the second needs customization.

If anyone has interest in contracting with me please PM me. (Where is the PM capability in this forum? :smile: )

Sam

Click the person's name / avatar and there is a SEND MESSAGE on the popup

I don't think they need customization. What were you seeing that you were concerned about?

There were several commented lines in the second procedure SP_GetErrors...

So I run these two procedures, in order?

PS: I don't get the SEND MESSAGE Kristen mentions when I click a user's avatar.

Sam

Correct. You create the extended event session first and then use the sp_GetErrors to query the session.

I often test with SELECT 1/0.

:smile: It took me a moment... SELECT 1/0... did Graz mean SELECT IO? Is this a new command? Something in the stored proc... then it clicked.

Snazzy forum, and the alert is interesting. Can I get the SEND MESSAGE enabled on my account so I can PM users?

Sam

Sounds like you need to post X-Messages before that is allowed, or perhaps a MOD has to enable it for you ...

I have a MESSAGE button now.

Thanks Graz

And just to clear this up... you do need to post X messages. We had a similar thing on the old site that I hacked together. I've been manually upgrading people from the old forums as I find them.

1 Like

Nothing to see here.