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_USERfor 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_USERmode.
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!
