BizTalk ESB Toolkit issue: EsbExceptionDb in single user mode

Last week, while trying to configure a brand-new BizTalk Server 2020 environment and resolving a strange ESB Toolkit issue (which I will discuss on another occasion), we encountered a minor SQL issue that, of course, was affecting BizTalk Server.

The EsbExceptionDb database gets stuck in this single-user mode:

EsbExceptionDb (Single User)

Cause

If your SQL Server database is stuck in SINGLE_USER mode, it means only one session or connection is allowed to access the database at a time, and often, that one session is being held by something else, making it difficult to change.

Common causes for that to happen are:

  • Someone manually set it to SINGLE_USER for maintenance and forgot to revert.
  • An unclosed session or management tool (like SSMS) is holding the single connection.
  • A rollback is in progress and is blocking changes.

In our case, my coworker attempted to restore an old version of the database, but the attempt failed, leaving the database in its current state.

Solution

The solution is quite simple:

  • You need to first find and KILL all the processes that are currently running.
USE master
GO

DECLARE @killProcess varchar(max) = '';
SELECT @killProcess = @killProcess + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('EsbExceptionDb')

EXEC(@killProcess);
GO
  • Then, you need to set the database in MULTI_USER mode.
USE master
GO

SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [EsbExceptionDb] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [EsbExceptionDb] SET MULTI_USER WITH ROLLBACK IMMEDIATE
GO

Where can I download it?

You can download the complete SQL Script code here:

Hope you find this helpful! So, if you liked the content or found it helpful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego! 

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top