How to Write Errors in Error Log? – Interview Question of the Week #175

Question: How to Write Errors in Error Log?

Answer: This question was asked by my one of my customer during SQL Server Performance Tuning Practical Workshop. The workshop is really fun as during the workshop we usually discuss quite a lots of things along with SQL Server Performance Tuning subjects.

How to Write Errors in Error Log? - Interview Question of the Week #175 writeerrorlog

During the workshop, we were talking about how error logs works and how various errors are logged into the error log. One of the attendees asked if there is any way we can create our own error and write that to error logs.

Absolutely, we can do that. Let us see a simple example where we have created a divide by zero error and the same error is logged into the error logs with the help of RAISEERROR.

BEGIN TRY
SELECT 1/0
END TRY
BEGIN CATCH
DECLARE @Var VARCHAR(100)
SELECT ERROR_MESSAGE()
SELECT @Var = ERROR_MESSAGE()
RAISERROR(@Var, 16,1) WITH LOG
END CATCH

Once you run above code, you can directly go to your error log and open it. You will find one of the recent entry will be of this error.

How to Write Errors in Error Log? - Interview Question of the Week #175 errorlogentry

Please note that when we specify the keywords WITH LOG with RAISERROR, it makes the entry in the error log. Additionally, if we specify severity above 16, SQL Server will automatically disconnect the session as well.

Have you ever written to error log with this command? If yes, please leave a comment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
How to Generate Fibonacci Series for the First 1000 Values? – Interview Question of the Week #174
Next Post
How to Enable Files & Folders Level Auditing in SQL Server? – Interview Question of the Week #176

Related Posts

1 Comment. Leave new

  • I have below code:

    BEGIN TRY
    DECLARE @INPUT DECIMAL
    SET @INPUT = 51.21
    DECLARE @VALUE NUMERIC(4,3)
    SET @VALUE = CONVERT(NUMERIC,@INPUT)
    PRINT @VALUE
    END TRY
    BEGIN CATCH
    Print Error_Message() ;
    THROW
    END CATCH

    — Now I want to print the value of the @INPUT variable in CATCH statement.
    How can I get this?

    Reply

Leave a Reply

Menu