Menu
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?
506
Another way to see if your database is in use is to look and see if the indexes are being used. Information on index usage is held in the sys. dm_db_index_usage_stats table since the last server reboot, and can be queried using this statement which can be tailored to select the data you need.
If you want to delete the database, you will get this error if there is an open session on the database. First, set the database to single_user mode. Then you can delete it.
To stop using a database, you will need to change your database context. For example, if you are trying to drop your database and you are in the context of that database, simply switch to another database (commonly master or tempdb ).
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
