Logging Erroneous Queries Only on SQL Server

Logging erroneous queries only on SQL server

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.

SQL Server Query log for failed/incorrect queries?

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

SQL Server logging failed queries

I've found that XEvents are great for monitoring events as they happen. However, they don't provide a mechanism to "handle" observed events. To fill that gap, I've used Event Notifications. I often describe them as asynchronous DDL triggers. I'll let you decide if that tl;dr definition is accurate or not.

If you want to give event notifications a try, here is a script that you can start with (sorry, it's really long). Let me know if you have any questions/issues. I'll try to reply as best as I can.

--Create these objects within a database that has service broker enabled.
USE DbaData
GO

--Drop objects first before trying to create them (in specific sequence).
IF EXISTS (
    SELECT *
    FROM sys.services
    WHERE name = 'svcUserErrorReportedNotification'
)
    DROP SERVICE svcUserErrorReportedNotification;
GO

IF EXISTS (
    SELECT *
    FROM sys.service_queues
    WHERE name = 'queUserErrorReportedNotification'
)
    DROP QUEUE queUserErrorReportedNotification;
GO

IF EXISTS (
    SELECT * 
    FROM sys.server_event_notifications 
    WHERE name = 'enUserErrorReportedEvents'
)
    DROP EVENT NOTIFICATION enUserErrorReportedEvents
    ON SERVER
GO

--Create a queue just for user error events.
CREATE QUEUE queUserErrorReportedNotification
GO

--Create a service just for user error events.
CREATE SERVICE svcUserErrorReportedNotification
ON QUEUE queUserErrorReportedNotification ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
GO

-- Create the event notification for user error events on the service.
CREATE EVENT NOTIFICATION enUserErrorReportedEvents
ON SERVER
WITH FAN_IN
FOR USER_ERROR_MESSAGE
TO SERVICE 'svcUserErrorReportedNotification', 'current database';
GO

IF EXISTS (
    SELECT *
    FROM INFORMATION_SCHEMA.ROUTINES r
    WHERE r.ROUTINE_SCHEMA = 'dbo' AND r.ROUTINE_NAME = 'ReceiveUserErrorReportedEvent'
)
    DROP PROCEDURE dbo.ReceiveUserErrorReportedEvent 
GO

CREATE PROCEDURE dbo.ReceiveUserErrorReportedEvent
/*****************************************************************************
* Name     : dbo.ReceiveUserErrorReportedEvent
* Purpose  : Runs when there is a USER_ERROR_MESSAGE event.
* Inputs   : None
* Outputs  : None
* Returns  : Nothing
******************************************************************************
* Change History
*   11/28/2016  DMason  Created
******************************************************************************/
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @MsgBody XML

    WHILE (1 = 1)
    BEGIN
        BEGIN TRANSACTION

        -- Receive the next available message FROM the queue
        WAITFOR (
            RECEIVE TOP(1) -- just handle one message at a time
                @MsgBody = CAST(message_body AS XML)
                FROM queUserErrorReportedNotification
        ), TIMEOUT 1000  -- if the queue is empty for one second, give UPDATE and go away
        -- If we didn't get anything, bail out
        IF (@@ROWCOUNT = 0)
        BEGIN
            ROLLBACK TRANSACTION
            BREAK
        END 
        ELSE
        BEGIN
            --Grab some relevant items from the message body XML (it is EVENTDATA(), btw)
            DECLARE @Login SYSNAME;
            DECLARE @ErrMsgText VARCHAR(MAX);
            DECLARE @ApplicationName VARCHAR(MAX);
            DECLARE @Severity INT;
            DECLARE @ErrorNumber INT;
            DECLARE @DBName SYSNAME;

            SET @Login = @MsgBody.value('(/EVENT_INSTANCE/LoginName)[1]', 'VARCHAR(128)' );
            SET @ErrMsgText = @MsgBody.value('(/EVENT_INSTANCE/TextData)[1]', 'VARCHAR(MAX)' );
            SET @ApplicationName = @MsgBody.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'VARCHAR(MAX)' );
            SET @Severity = @MsgBody.value('(/EVENT_INSTANCE/Severity)[1]', 'INT' );
            SET @ErrorNumber = @MsgBody.value('(/EVENT_INSTANCE/Error)[1]', 'INT' );
            SET @DBName = @MsgBody.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'VARCHAR(128)' );

            --Do stuff here.
            --Log to a table, etc.

            /*
                Commit the transaction.  At any point before this, we 
                could roll back -- the received message would be back 
                on the queue AND the response wouldn't be sent.
            */
            COMMIT TRANSACTION
        END
    END
END
GO

ALTER QUEUE dbo.queUserErrorReportedNotification 
WITH 
STATUS = ON, 
ACTIVATION ( 
    PROCEDURE_NAME = dbo.ReceiveUserErrorReportedEvent, 
    STATUS = ON, 
    MAX_QUEUE_READERS = 1, 
    EXECUTE AS OWNER) 
GO

Can SQL Server automatically log stored procedure errors

If the SPs are being called from code in a separate data layer, you could possibly add a global exception handler for that class. There is no 'global' error handling, per-se in sql server as far as stored procedures go - think about code - in code, if you had a gazillion classes, and there was no ineritance of any sort, you would have to implement error handling on each class separately. Sql server SPs have their own error handling, such as try-catch and @@error - look on books online, or http://www.codeproject.com/KB/database/ErrorHandling.aspx

How to clear error logs using query in SQL Server without restarting the instance?

Reference : How to Recycle SQL Server Error Log file without restarting SQL Server Service

Use [master];
GO

SP_CYCLE_ERRORLOG
GO

---

Related Topics