How to Drop Database in SQL Server

There are times when you need to drop a database in SQL Server, but there are active connections preventing you from doing so.

In this article, we’ll show you how to close those connections and drop the database.

Method 1: Using the KILL Command

The first method is to use the KILL command. This will terminate all active connections to the database, allowing you to drop it. However, this method can cause issues with your applications, so be sure to test before using it in production.

To use the KILL command, connect to your SQL Server instance and run the following query:

KILL DATABASE <database name>;

Replace <database name> with the name of the database you want to drop.

Method 2: Using the sp_who2 Stored Procedure

The second method is to use the sp_who2 stored procedure. This procedure will show you all of the active connections to your SQL Server instance. From there, you can identify the connections that are preventing you from dropping the database and kill them manually.

To use this method, connect to your SQL Server instance and run the following query:

sp_who2;

This will return a list of all active connections. Look for any that are associated with the database you want to drop and kill them using the KILL command (shown in Method 1).

Method 3: Using the sys.databases View

The third method is to use the sys. Databases view. This view contains information about all of the databases on your SQL Server instance. By querying this view, you can identify the database you want to drop and its status. If the database is online, you will not be able to drop it. However, if the database is offline, you can drop it without issue.

To use this method, connect to your SQL Server instance and run the following query:

SELECT * FROM sys.databases;

This will return a list of all databases on your SQL Server instance, along with their status. If the database you want to drop is listed as offline, you can drop it without issue.

Read Also : Mobile App Trends

It is not recommended to drop a database by simply closing all connections to it. However, if you absolutely need to do this, here are the steps:

1. Connect to the SQL Server instance using SSMS or other tools.

2. Expand the “Databases” folder in Object Explorer.

3. Right-click on the database to be dropped and select “Properties”.

4. In the Database Properties dialog, go to the “Options” page.

5. Scroll down to the “State” property and change it from “Online” to “Restricted User Access”.

6. Click OK to save the changes.

7. Now that the database is in “Restricted User Access” mode, all new connections will be rejected.

8. Wait for all existing connections to be closed. This may take some time depending on the activity of the database.

9. Once all connections are closed, right-click on the database again and select “Delete”.

10. In the Delete Object dialog, select the “Close existing connections” checkbox and click OK.

11. The database will be dropped and removed from the “Databases” folder.

SQL Server Management Studio: To drop a database in SQL Server Management Studio, follow these steps:

1. Connect to the SQL Server instance using SSMS.

2. In Object Explorer, expand the “Databases” folder.

3. Right-click on the database to be dropped and select “Delete”.

4. In the Delete Object dialog, select the “Close existing connections” checkbox and click OK.

5. The database will be dropped and removed from the “Databases” folder.

Transact-SQL: To drop a database in Transact-SQL, use the DROP DATABASE statement:

DROP DATABASE <database_name>;

For example, to drop the “test” database, use the following statement:

DROP DATABASE test;

Note: Be very careful when using the DROP DATABASE statement! Once a database is dropped, it cannot be recovered.

Conclusion

In this article, we’ve shown you three methods for dropping a database in SQL Server when there are active connections. Be sure to test these methods before using them in production, as they can cause issues with your applications.