Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Logging erroneous queries only on SQL server

I have what would seem to be an easy goal to accomplish, yet I have not found a good solution. Google does not shed a light on it and I just hope that I have been looking for a solution in wrong places or just tried to use tools in a wrong way...

Or perhaps it is already too late for me today to think clearly :) But this is where you can help me out, I hope.

I need to be able to log erroneous queries only which were executed on a specific instance of SQL Server. I thought that SQL Profiler would allow me to do it in no time, but I have not been able to find a combination of settings that would allow me to log only those queries, that returned an error (most likely parsing error) + error details.

Imagine SQL Server with 100s of queries being executed each second, and only 1 or 2 improperly formed queries each day resulting with a parsing error. I just need to be able to come and see those 1-2 queries per day without having to run a very expensive profile for all queries on the system and having to dig through it each day.

Is that possible at all?

like image 838
Sebastian Zaklada Avatar asked Mar 04 '11 22:03

Sebastian Zaklada


People also ask

How do you log errors in SQL Server?

In this article View the SQL Server error log by using SQL Server Management Studio or any text editor. For more information about how to view the error log, see Open Log File Viewer. By default, the error log is located at Program Files\Microsoft SQL Server\MSSQL. n \MSSQL\LOG\ERRORLOG and ERRORLOG.

Where are error logs for SQL Server?

By default, the error log files are stored in the following location: Program Files\Microsoft SQL Server\MSSQL<n>. <instance name>\MSSQL\LOG\ERRORLOG, where <n> is an archived version number, and <instance name> is the name of the instance. This is only the default location.


1 Answers

Set up a trace for the Exception Event Class, SQL:BatchCompleted Event Class and RPC:Completed Event Class. Add a filter for Error not equal to 0. Start the trace. Done.

like image 73
Remus Rusanu Avatar answered Sep 21 '22 12:09

Remus Rusanu