SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Finding queries that throws errors using Profiler
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/17/2013 :  03:42:05  Show Profile  Reply with Quote
Friends,

I'm currently helping out on a somewhat large sharepoint-like system (10 databases, lots of tables, procedures, etc) made in .NET and we see in the .net-logs that there are some errors that are thrown from the database because of badly formed queries and some referential integrity problems. I have no control over the .net code but I've been asked if I can try to find the actual queries that are generating errors but for some reason I haven't been able to find them! I get several hundred error messages in profiler every day, but I can't seem to configure profiler so that I get the actual offending queries as well. Do any of you have any good tricks up your sleeves?

- Lumbago
My blog-> http://thefirstsql.com

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 06/18/2013 :  14:36:46  Show Profile  Reply with Quote
One thing about catching errors in profiler is that the default events for t-sql, statement, and batch is "completed". But depending on the error the comleted event may not be fired. Try changing to a "starting" event instead.

EDIT:
hmm - I was just confirming that and see that for a few simple errors I created I did see trace rows for "SQL:BatchCompleted". So that may not be it.

Couple obvious questions:
- Are you sure profiler was running against the (right) server at the time of an error?
- Do you have an event selected that will be traced for these statements?
- Could it have been captured but there is so much other trace traffic that you couldn't locate the statement?

Be One with the Optimizer
TG

Edited by - TG on 06/18/2013 14:41:47
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 06/19/2013 :  09:57:22  Show Profile  Reply with Quote
First of all; thanks for replying! Hehe... Seems like this isn't something people do on a daily basis (me included!)

I tried to configure my traces using all kinds of variations but I always included the Batch/Statement/RPCStarting. The problem is that there is no error at the time of the Starting-event, and the queries that raises errors either doesn't have the Error flag set or the completed event isn't executed at all.

What I ended up doing was basically to trace everything that had i.e. "Interests" (I knew from the logs that some of the errors were related to this table) in the TextData column for an hour or so, and saved the trace data in a sql server table. Then after the trace stopped I analyzed the table and "fortunately" there were a few incidents of the error that led me to the offending query.

But I have to say thet running a trace like this on a production system with thousands of queries running all over the place had my heart really pounding. I wouldn't recommend it...

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 06/19/2013 :  13:27:19  Show Profile  Reply with Quote
Good - glad you found the statements.
Yeah, fortunately I don't have to deal with this often but what I have done is filter OUT ntlogins that I know aren't responsible and saving the the trace to a file or table as you did. Sometimes, if you have an eventlog error or some idea of a timestamp the error occurred you just have to correlate by time and hopefully narrow the possibilities to just a few statements. We had an architect here for awhile that was big on entity framework - ugh what a nightmare to trouble shoot.

Be One with the Optimizer
TG
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000