Drop Database Error For Azure Synapse Database

user_3995660 0
Viewed 2326

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.

azure-synapse
3 Answers

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
  1. 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
  1. Drop the database from Synapse Studio, not from SSMS
user_181810270

Were you connected to MASTER?

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

user_26496260

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
user_143687910
Related