How to find the currently in use process and KILL the process in SQL Server 2012

How to manage when you get the message like “because it is currently in use”?

For Example, when I try to Drop database, getting following error message.

In this situation, first you have to check which process is currently running on particular database and KILL the process.

SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('RegularDB')

KILL is commonly used to terminate a process that is blocking other important processes with locks, or a process that is executing a query that is using necessary system resources. System processes and processes running an extended stored procedure cannot be terminated. Use KILL very carefully, especially when critical processes are running.

KILL { session ID | UOW } [ WITH STATUSONLY ]

KILL WITH STATUSONLY does not terminate or roll back the session ID or UOW; the command only displays the current progress of the rollback.

To kill the process run: KILL 57

To check the status of KILL process: KILL 57 WITH STATUSONLY;

Another alternative approach is making the database as Single User. This process closes all the existing connections.

USE master;

GO

ALTER DATABASE RegularDB

SET SINGLE_USER

WITH ROLLBACK IMMEDIATE;

GO

DROP DATABASE RegularDB;

Cheers!