Changes to the state or options of database '' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.-Resolved - SQL Server Errors

Scenario:

I was in process of preparing the scripts to rename a databases on my SQL Server Instance. Instead of killing connections for each Database by using Cursor , I decided to put my Database in Single Mode user and then rename and wanted to bring it to multi user mode after that.

Here is the query I used to put the database into Single User mode.

Alter database [TechBrothersIT-A] set single_user 

with rollback immediate

It did put the Database in single user mode,When i tried to rename the database, I received below error.

EXEC sp_renamedb 'TechBrothersIT-A','TechBrothersIT'

Msg 924, Level 14, State 1, Line 27
Database 'TechBrothersIT-A' is already open and can only have one user at a time.

I start searching for the process which is using the database.

I execute sp_who2 but did not see any process running on this databases.

Select * from sys.sysprocesses
where DB_Name(dbid)='TechBrothersIT-A'

Did not find any process running on database. I thought let me put the database back to multi user mode, used below query.

ALTER DATABASE [TechBrothersIT-A] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Got below error

Msg 5064, Level 16, State 1, Line 32
Changes to the state or options of database 'TechBrothersIT-A' cannot be made at this time.

 The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 32
ALTER DATABASE statement failed.

Solution:

Instead of using sp_who2 and sys.sysprocesses, I tried the below query with dmv 

Query1: 

SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('TechBrothersIT-A') 

In may case, it returned me 55.

I wanted to figure out what query it is running. Anyways I executed below query to get more information for query text for spid. 

DECLARE @query VARBINARY(128)
SELECT @query = sql_handle
FROM sys.sysprocesses
WHERE spid = (55)
SELECT TEXT
FROM sys.dm_exec_sql_text(@query)

Below is the output I got from above query but that does not tell if it is related to database which I am trying to put into multi user mode.

DECLARE @edition sysname; SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname); select case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType'

Anyways, I went ahead and killed the spid which was returned by Query1 above.

Kill 55

After that I execute

ALTER DATABASE [TechBrothersIT-A] SET MULTI_USER 

WITH ROLLBACK IMMEDIATE

and it put the database in multi user mode without any problem.

Lesson To learn:

Instead of using sp_who and sys.sysprocesses to find spid, use Query1 and then kill it. After that you should be good to rename and put database in multi user mode.