9

I have a Database on SQL Server 2008, which I want to drop. Currently it is in single user mode and it is currently in use.

select * from sys.sysprocesses

returns

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

and I do not know how to identify the session I have to kill.

An attempt to set it offline

ALTER DATABASE G_MAIN_DE SET OFFLINE WITH ROLLBACK IMMEDIATE

yields

Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'G_MAIN_DE' 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 1
ALTER DATABASE statement failed.
Improve this question
1
  • Thought I would add, in SQL Server 2016, sp_who returns some rows but an error saying the database is already open and can only have one user, etc - but the rows returned do not show the session that is tying up the database. sp_who2 does not return any rows at all, but only the error. sp_whoisactive does not return rows either, but two messages "Warning: The join order has been enforced because a local join hint is used." and then the error about the database being open "and can only have one user at a time." - in case anyone searches for those 3 proc names.
    – youcantryreachingme
    Jul 30, 2020 at 0:10

4 Answers 4

Reset to default
16

To identify the session currently using the database you can try

SELECT request_session_id
FROM   sys.dm_tran_locks
WHERE  resource_database_id = DB_ID('G_MAIN_DE')
Improve this answer
1
  • 2
    Thank you, thank you, thank you. Finally there is peace in our land.
    – earthling42
    Jan 13, 2016 at 15:48
10

This built-in stored procedure shows all connections to a database

exec sp_who2

And this query kills a connection to the database, for example connection SPID #53

exec kill 53
Improve this answer
3
  • 2
    Note: The command is just kill 53, there's no exec at the beginning.
    – vaindil
    May 4, 2018 at 21:05
  • why is this stored procedure sp_who2? what happened to sp_who? Is sp_who2 an updated version or something?
    – rpivovar
    Dec 19, 2019 at 5:31
  • It can happen that sp_who, sp_who2 and sp_whoisactive fail to return any rows, but only show the same error about the database allowing only one user, etc. (Well actually sp_who does return some rows but no rows for the affected database).
    – youcantryreachingme
    Jul 30, 2020 at 0:13
3

The easiest thing to so is use SSMS Activity Monitor (Right-click server/Activity Monitor/Processes). Sort by Database. Kill whatever process has a hold onthe DB. You might also want to issue the kill command right before you set the DB to single-user and do the drop.

Improve this answer
-2

if above process is not working,then go to services and kill the sqlservices in taskmanger. Then will be able to set to multiuser.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.