CODEWITHSUNDEEP
sqlsql-server-2008logging
Joe
Joe

Reputation: 185

SQL Server Query log for failed/incorrect queries?

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.

Upvotes: 16

Views: 32557

Answers (2)

Salvador L
Salvador L

Reputation: 121

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;

Upvotes: 6

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

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...

Upvotes: 10

Related Questions