I have an open single-user session on a SQL Server 2016.
All other queries work, but fn_listextendedproperty
fails with Database 'EIP_TIM' is already open and can only have one user at a time.
It looks a bit like it tries to create a second connection internally.
The Activity Monitor looks like this:
(the selected connection is my current one)
Note that there have been schema-modifying statements after alter database EIP_TIM set single_user
.
If I run just exactly the following block, then it succeeds.
alter database EIP_TIM set multi_user;
alter database EIP_TIM set single_user with rollback immediate;
select count (*) from tabObject;
SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE', N'tabAttribImage', N'COLUMN',N'AttribImageID');
But this is all part of a larger update script, and in that situation it fails.
I know this question is at the moment a bit generic. I first wanted to ask if there are any known issues with fn_listextendedproperty
in combination with single_user mode.
If you need any other information, please let me know.
Best Answer
Apparently the big bold warnings were not just for show - setting
AUTO_UPDATE_STATISTICS_ASYNC OFF
fixed the issue.