How to get system/error messages with severity level lower than 19 logged to Application Log

Have you noticed that SQL Server does not send all messages to Application Log? The default setting does not send all messages, generally messages with severity level between 19 and 25 are automatically written but not all. The best way to check which messages that are getting logged, is checking sys.messages catalog view. This has all messages and the column is_event_logged indicates whether the messages will be logged or not.

Now, what if you need to get a message logged which is not get logged automatically? Good example is, a message related to deadlock. If you check this message, you will see that its is_event_logged is not set to 1.

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

If required, this can be enabled for logging. It can be done using sp_altermessage stored procedure. Here is the way of doing it.

EXECUTE sp_altermessage 1205, 'WITH_LOG', 'True';

Now it is enabled. If you run the first code again, result will be just like this:

Let's test this. Below code generates a deadlock.

USE master;
GO

-- Create a sample database
IF EXISTS (SELECT * FROM sys.databases WHERE name = 'DeadlockTestDB')
 DROP DATABASE DeadlockTestDB;
GO

CREATE DATABASE DeadlockTestDB;
GO


-- Add two tables and insert two records
USE DeadlockTestDB;
GO

CREATE TABLE Table01 (Id int PRIMARY KEY, Name varchar(200));
GO
INSERT INTO Table01 VALUES (1, 'Table01 value');
GO

CREATE TABLE Table02 (Id int PRIMARY KEY, Name varchar(200));
GO
INSERT INTO Table02 VALUES (100, 'Table02 value');
GO


-- Create two stored procedures.
-- This one updates a record in Table01, waits, 
-- and updates Table02.
CREATE PROC UpdateTable01andTable02
AS
BEGIN

 BEGIN TRAN

  UPDATE Table01
   SET Name = 'Updated'
  WHERE Id = 1;

  WAITFOR DELAY '00:00:10';

  UPDATE Table02
   SET Name = 'Updated'
  WHERE Id = 100;

 COMMIT TRAN
END;
GO

-- This one updates a record in Table02 and Table01
CREATE PROC UpdateTable02andTable01
AS
BEGIN

 BEGIN TRAN

  UPDATE Table02
   SET Name = 'Updated - 2'
  WHERE Id = 100;


  UPDATE Table01
   SET Name = 'Updated -2'
  WHERE Id = 1;

 COMMIT TRAN
END;
GO

/*

-- Open a new connection and run this code
USE DeadlockTestDB;
GO

EXEC UpdateTable01andTable02;
GO

-- Open another connection and run this.
-- Make sure this run just after you start the above execution
USE DeadlockTestDB;
GO

EXEC UpdateTable02andTable01;
GO
*/

Once you get the code executed and received the deadlock error, open the Event Viewer and see. You should see something like this;