Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.
share|improve this question
    
have you tried taking it offline first? –  DrCopyPaste Sep 6 '13 at 9:18
    
maybe this link can help you? remidian.com/2008/01/… –  DrCopyPaste Sep 6 '13 at 9:21
    
Yes is not possible. –  bernd_k Sep 6 '13 at 9:21
    
Does SELECT request_session_id FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID('G_MAIN_DE') return anything? –  Martin Smith Sep 6 '13 at 9:22
    
@martin Smith yes it does –  bernd_k Sep 6 '13 at 9:24

3 Answers 3

up vote 5 down vote accepted

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') 
share|improve this answer

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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