SQL Server Single User Mode – fn_listextendedproperty Fails

sql serversql-server-2016

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.

enter image description here

The Activity Monitor looks like this:
enter image description here
(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.

Related Question