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.

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.

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)