Problem Description:

Database restore on non-prod database failed with below error.

Msg 3101, Level 16, State 1:
Server 'ASESRV', Line 1:
Database in use. A user with System Administrator (SA) role must have exclusive use of database to run load.

Possible Resolution:

Case1. First check for any active connection on that specific database and kill it after confirming with Application team.

select 'kill', spid from sysprocesses where dbid =db_id('ASEDB')

Case2. In case you are not able to see any active connection on that database and restore is still failing with same 'database in use' error, then run below DBCC command to check for any active user connection.

1> dbcc dbreboot('report','ASEDB')
2> go
———- Active Processes and Transactions in Database 'ASEDB'———-

Spid Program Transaction Status CPU I/O Blk Error
200 isql recv sleep 0 0 0 0

———- Operation on Database 'ASEDB' Completed Successfully ———-

Case3. Most of the time, database in use error is resolved in above 2 cases. In case if above solution does not work for some reason, then we can run below command to fix the issue.

1>dbcc dbcacheremove('ASEDB')
2>go