SQL Server: Setting database mode to RESTRICTED_USER WITH ROLLBACK IMMEDIATE doesn't always drop all connections

Question

I need to perform a restore of database from .NET using SMO.Prior to the restore I set the database into RESTRICTED_USER WITH ROLLBACK IMMEDIATE mode. This works fine during testing/debugging - all users are dropped. However, when this code is run as part of an automated process at night, sometimes I get the following exception:

Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Exclusive access could not be obtained because the database is in use. RESTORE DATABASE is terminating abnormally

What's happening here? Thanks!

Answer 1

Can you not do;

alter database <db_name> set single_user with rollback immediate

Then add another TSQL step after the backup:

alter database <db_name> set multi_user

Alternatively, to find out what could be happening as this is running at night, somehow capture the results of

EXEC sp_who2

Which would show you who is connected at the time.