SQL Server tells me database is in use but it isn't
SQL Server keeps telling me a database is in use when I try to drop it or restore it, but when I run this metadata query:
select * from sys.sysprocesses
where dbid
in (select database_id from sys.databases where name = 'NameOfDb')
It returns nothing.
Sometimes it will return 1 process which is a CHECKPOINT_QUEUE waittype. If I try to kill that process, it won't let me (cannot kill a non-user process).
Anyone have any idea what's wrong?
Answer
i like this script. Do not struggle with killing..
use master
alter database xyz set single_user with rollback immediate
restore database xyz ...
alter database xyz set multi_user