SQL Server – “Lock Timeouts” – ErrorLog

Background

Unlike some other database platforms database lock timeouts are not written into SQL Server Error Log.

 

Why

Let us quickly see why?

Code


set nocount on;
go

declare @tblError TABLE
(
	[errorNumber] int
)

declare @languageID int

set @languageID = 1033

/*
	Get list of error numbers
*/
insert into @tblError
(
	[errorNumber] 
)
select 1222

/* 
	Using master.dbo.sysmessages 
*/
select 

		  tblSM.[error]
		, tblSM.[description]
		, tblSM.[severity]
		, tblSM.[dlevel]
		, isEventLogged
			= CONVERT(INT,dlevel & 0x80) / 128 

from   master.dbo.sysmessages tblSM

inner join @tblError tblE
		on tblSM.error = tblE.errorNumber

where  tblSM.msglangid = @languageID 


/* 
	Using sys.messages 
*/
SELECT 
		  [messageID] = message_id
		, tblSM.severity
		, [isEventLogged] = is_event_logged
		, [text] = [text]

FROM sys.messages  tblSM

inner join @tblError tblE

		on tblSM.[message_id] = tblE.errorNumber

where  tblSM.language_id = @languageID 

Output

Explanation

We can quickly see that the error number, 1222, that corresponds to “Lock request time out period exceeded” is not logged, by default.

 

Log

Code


use [master]
go

set nocount on;
set XACT_ABORT on;
go

declare @tblError TABLE
(
	  [id]		    int not null
						identity(1,1)

	, [messageID]   int not null

)

declare @id int
declare @iMessageID int
declare @strMessageID	varchar(10)
declare @strMessageText nvarchar(600)
declare @languageID int

declare @idMax			int
declare @isEventLogged	bit

declare @FORMAT_EVENT_LOGGED_ALREADY varchar(600)
declare @FORMAT_EVENT_LOGGED_ACTION varchar(600)
declare @strLog						 nvarchar(600) 

declare @CHAR_LINE_BREAK					varchar(600)

declare @commit								bit

set @commit = 0
--set @commit = 1

set @languageID = 1033
set @FORMAT_EVENT_LOGGED_ALREADY = 'Error %s with message "%s" is already being logged!'
set @FORMAT_EVENT_LOGGED_ACTION = 'Error %s with message "%s" will now be logged!'

set @CHAR_LINE_BREAK = replicate('=', 120)

insert into @tblError
(
	[messageID] 
)
select 1222

set @id = 1
set @idMax = ( select max([id]) from @tblError )



begin tran

	select 
			   = 'Before'
			, tblErr.messageID
			, tblSM.[text]
			, [isEventLogged] = tblSM.is_event_logged

	from    @tblError   tblErr

	inner join sys.messages  tblSM

			on tblErr.messageID = tblSM.message_id

	where tblSM.[language_id] = @languageID  

	order by
			tblErr.[messageID] asc


	while (@id <= @idMax) begin select @iMessageID = tblErr.messageID , @strMessageText = tblSM.[text] , @isEventLogged = tblSM.is_event_logged from @tblError tblErr inner join sys.messages tblSM on tblErr.messageID = tblSM.message_id where tblSM.[language_id] = @languageID and tblErr.[id] = @id set @strMessageID = cast(@iMessageID as varchar(6)) if (@isEventLogged = 1) begin exec master.dbo.xp_sprintf @strLog output , @FORMAT_EVENT_LOGGED_ALREADY , @strMessageID , @strMessageText print @strLog end else begin exec master.dbo.xp_sprintf @strLog output , @FORMAT_EVENT_LOGGED_ACTION , @strMessageID , @strMessageText print @strLog EXECUTE sp_altermessage @iMessageID , 'WITH_LOG' , 'true' ; end print @CHAR_LINE_BREAK set @id = @id + 1 end select  = 'After' , tblErr.messageID , tblSM.[text] , [isEventLogged] = tblSM.is_event_logged from @tblError tblErr inner join sys.messages tblSM on tblErr.messageID = tblSM.message_id where tblSM.[language_id] = @languageID order by tblErr.[messageID] asc print ''; print ''; while (@@TRANCOUNT > 0)
begin

	if (@commit = 1)
	begin

		print 'commit'
		commit tran

	end
	else
	begin

		print 'rollback'
		rollback tran

	end

end -- while (@@TRANCOUNT > 0)

go

 

Output

 

Error Log

Observe

Now whenever lock timeouts occur, it will be logged in SQL Server ErrorLog

Explanation

Obviously we do not have any metadata about the Lok timeout.  All we have is a timestamp.

 

References

  1. Jon Gurgul
    • Suppress messages in SQL log
      Link
  2. Jason Strate
    • Lost In Translation – Deprecated System Tables – Sysmessages
      Link

 

One thought on “SQL Server – “Lock Timeouts” – ErrorLog

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s