SQL Server Query log for failed/incorrect queries?

Question

I am using SQL Server 2008 and I would like to know if there's a way to find invalid SQL queries that have been executed in a database. For example:

SELECT * FROM NonExistingTable

...where the database didn't exist or a SELECT/INSERT/UPDATE with incorrect syntax.

Solution 1

SQL Server doesn't keep a log of these things, so if you want to capture them you'll have to do so using a server-side trace with a filter to only capture statements with errors. It will be quite an expensive trace, and you'll get some false positives if you do things like RAISERROR WITH NOWAIT... I guess it's easier than implementing TRY/CATCH everywhere and logging the errors yourself?

There may be ways to do it with SQL Server Audit (depending on your edition) or Extended Events but I haven't tried to do this specific thing with either...

Solution 2

Logging of the message 229 severity 14 would definitely help you identify when these errors take place.

SELECT * FROM master.sys.messages
 WHERE language_id=1033
 AND severity=14
 AND message_id=229;

You can enable it by using:

EXEC sp_altermessage 229, 'WITH_LOG', 'true';

I'd create an alert on Severity 14 errors to be notified when they happen, for that you'd need to set an operator too.

The only limitation that this has is that it does not give you the login, host name or IP address of the session that had the error. It does log the SPID and you'd have to retrieve it by using EXEC xp_readerrorlog by using something like

EXEC xp_readerrorlog 0,1,'permission',NULL,NULL,NULL,'desc'

or opening it on SSMS and then correlate it with what you find on sysprocesses using

SELECT * FROM master.dbo.sysprocesses WHERE SPID='LoggedSPID'

You can enable the logging of other messages you may want to be aware of, for this purpose, primarily dig on severity 14 and enable as needed.

SELECT * FROM master.sys.messages
 WHERE language_id=1033
 AND severity=14;

Comments

[Joe]

Thanks Aaron for your response. If it is not possible to get a log afterwards I will have to go fora profiling approach instead. The problem is that this doesn't happen for men when I try to reproduce the error. It only happens once in a while on another machine.

[Martin Smith]

@Joe - See my answer here for something that might get you started on the extended events route. This should be less overhead than tracing as the event only gets fired on error rather than capturing all SQL statements then filtering out the ones without errors.