Keep system sessions from entering a single-user database
I am super hesitant to put a database into singleuser mode without being in the database because system sessions (sessionid <50) will get into the database and I cannot get the sessions out. I can't kill them because they are system sessions and the KILL command doesn't work.
This comes into play when I am in the database, set the database into single_user mode, get out of the database and have a slight delay and then try to do something with the database (such as backup the log with norecovery).
Thank you @Tom Phillips. We are aware of restricted_user and we use it quite often, but there are times when you need to put a database into single_user and leave the database. It's not often, and we try to avoid it as much as we can, but it does happen. Unfortunately we dread it because system sessions will get into the database and prevent us from performing our actions.
Reading BOL for single_user (https://docs.microsoft.com/en-us/sql/relational-databases/databases/set-a-database-to-single-user-mode) it says that there can be system processes that can use the database if you have
FROM sys.databases AS d
WHERE d.is_auto_update_stats_async_on = 1
but none of our databases have that option turned on. We will continue to use restricted_user as much as we can, but we want to be able to have the option of not having to fear using a feature that should be helping us and not hurting us.
Tom Phillips commented
Use RESTRICTED_USER instead of SINGLE_USER mode.
Razvan Puscasu commented
We had the same problem, errorlog message
ex_raise2 - exception handler raised exception , major=9, minor=24, severity=14 - Server terminating
This is usually caused by monitoring apps that call sys.dm_exec_requests every second.
The problem started to appear after we applied CU6 on SQL2016
Todd Carrier commented