Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2013-06-17 : 03:42:05
|
| 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?- LumbagoMy blog-> http://thefirstsql.com |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-06-18 : 14:36:46
|
| 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 OptimizerTG |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2013-06-19 : 09:57:22
|
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...- LumbagoMy blog-> http://thefirstsql.com |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-06-19 : 13:27:19
|
| 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 OptimizerTG |
|
|
|
|
|
|
|
|
Finding queries that throws errors using Profiler