<\/p>\n
If you want to delete the database, you will get this error if there is an open session on the database.<\/p>\n
First, set the database to single_user mode. Then you can delete it.<\/p>\n
USE [master]\r\nGO\r\nALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nGO\r\nDROP DATABASE [Test]\r\nGO<\/pre>\nWith the above script, we close the open connections in the database and set the database as single_user so that only we can use it by ourselves and then delete it. If the database is already in single_user mode, you will get an error as follows.<\/p>\n
Msg 5064, Level 16, State 1, Line 3<\/em><\/span><\/p>\n
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.<\/em><\/span><\/p>\n
<\/p>\n
Msg 5069, Level 16, State 1, Line 3<\/em><\/span><\/p>\n
ALTER DATABASE statement failed.<\/em><\/span><\/p>\n
Msg 3702, Level 16, State 4, Line 5<\/em><\/span><\/p>\n
Cannot drop database “” because it is currently in use.<\/em><\/span><\/p>\n
To manually kill all sessions that use the database in single_user mode, you must run the following query. You should write your own database name instead of “Test”.<\/p>\n
select * from sys.sysprocesses where dbid=DB_ID('Test')<\/pre>\nThe records returned as a result of the query are the processes on this database. You can find and kill the session_ids that use your database. Then you can delete the database. For example, if the result is 55, kill 55.<\/p>\n
It will be safer to find and ask the person who sends the query before killing the query. If you think you don’t need to ask anyone, you can automatically kill all sessions in the database with the following script.<\/p>\n
declare @Sql varchar(1000), @databasename varchar(100)\u00a0\r\nset @databasename = 'You Must Write Your Database Name Here'\u00a0\r\nset @Sql = ''\u00a0\u00a0\r\nselect\u00a0 @Sql = @Sql + 'kill ' + convert(char(10), spid) + ' '\u00a0\r\nfrom\u00a0\u00a0\u00a0 master.dbo.sysprocesses\u00a0\r\nwhere\u00a0\u00a0 db_name(dbid) = @databasename\r\n\u00a0\u00a0\u00a0\u00a0 and\u00a0\r\n\u00a0\u00a0\u00a0\u00a0 dbid <> 0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0 and\u00a0\r\n\u00a0\u00a0\u00a0\u00a0 spid <> @@spid\u00a0\r\nexec(@Sql)\r\nGO<\/pre>\n\n<\/div>\n