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;
No comments:
Post a Comment