Drop Database Error For Azure Synapse Database

Question

When I try to drop a database in my builtin synapse pool, I'm getting the following error:

Cannot drop database "database name" because it is currently in use.

I've tried in both SSMS and Studio Synapse Studio and both returned errors.

I made sure there's no external datasources and file formats in the database.

The SSMS command I used was:

DROP DATABASE [database name]

Set Single_use mode doesn't work either. If you try this:

ALTER DATABASE [Database Name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

You'll get this:

SINGLE_USER is not supported for ALTER DATABASE.

What blocks a database from being dropped in synapse?

Thanks.

Lancers

Did you ever got a solution?. I'm having the same issue with same errors. – Struck

Answer 1

What worked for me is:

1. Run this in master

    select DB_NAME(database_id), 'kill '+cast(session_id as varchar(10)), *
    from sys.dm_exec_sessions
    where DB_NAME(database_id) NOT IN ('master')
    order by 1

2. Kill all sessions (active as well as sleeping) for the database you want to delete using the kill command returned by the query above. Run it in master:

    kill 82

3. Drop the database from Synapse Studio, not from SSMS

Lacreshalacrimal

Answer 2

Were you connected to MASTER?

Assuming there were no users connected, that is the only reason I can think it would be blocked.

Toxicant

Yes I was in the master database. – Lancers

Answer 3

Make sure to kill all the sessions manually.

One of the reasons why you could get this error is that there is an active connection via SSMS/ADS/Synapse Studio/Power BI or some other tool which is using that database at the moment.

When you close all the sessions, you should be able to delete the database successfully.

Here is the procedure for SQL serverless pool (aka SQL on-demand).

Step 1: Find the session which you want to kill using query bellow.

    SELECT 
     'Running' as [Status],
     Transaction_id as [Request ID],
     'SQL serverless' as [SQL Resource],
     s.login_name as [Submitter],
     s.Session_Id as [Session ID],
     req.start_time as [Submit time],
     req.command as [Request Type],
     SUBSTRING(
         sqltext.text, 
         (req.statement_start_offset/2)+1,   
         (
             (
                 CASE req.statement_end_offset  
                     WHEN -1 THEN DATALENGTH(sqltext.text)  
                     ELSE req.statement_end_offset  
                 END - req.statement_start_offset
             )/2
         ) + 1
     ) as [Query Text],
     req.total_elapsed_time as [Duration]
 FROM 
     sys.dm_exec_requests req
     CROSS APPLY sys.dm_exec_sql_text(sql_handle) sqltext
     JOIN sys.dm_exec_sessions s ON req.session_id = s.session_id 

Step 2: Use the value in the Session ID column to kill the process which you want to. For example, if the Session ID is 81 then execute the following command

 kill 81

---

Here is the procedure for SQL dedicated pool (aka SQL DW).

Step 1: Find the session which you want to kill using the bellow query

 SELECT * FROM sys.dm_pdw_exec_sessions
 where [status] = 'Active' and not sql_spid = @@SPID
 GO

Step 2: Use the value in the [session_id] column to kill the process which you want to. for example if the session_id is 'SID210' then execute the following command

 kill 'SID210'
 GO

Sowers

I used the query and only saw connections to the master DB where I was running the query you posted. I tried to drop the database and still got the same error. – Lancers

Can you try to delete the database from the Synapse Studio/Data activity hub? Do you have any running requests when you go to Synapse Studio/Monitoring/SQL requests? Are all in state completed? – Remitter

I tried both Synapse Studio and Data Activity Hub. Both returned errors. The databases were new hence no activity on them. – Lancers

Ok, then I would suggest creating a support request in Azure Portal where support engineers will provide solution for you since this should be investigated. – Remitter

For myself on Serverless I didn't have anything actively running anything against the DB but it still refused to drop. I used SELECT * from sys.dm_exec_sessions where database_id = DB_ID('MyDatabase') and killed the sleeping user sessions from that list and was able to drop. – Ruminate