How to drop DB in Synapse, since code from your "documentation" is not working?

Konstantin Kulikov 5 Reputation points
2023-02-27T14:28:14.6666667+00:00

What is the proper way to drop a DB in Azure Synapse?

Code from this documentation produces an error "SINGLE_USER is not supported for ALTER DATABASE".

2023-02-27 16_25_47-syn-ht-breakby-dev-sievo - Azure Synapse Analytics - Vivaldi

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,362 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Alberto Morillo 32,886 Reputation points MVP
    2023-02-27T15:29:38.54+00:00

    Kill all active sessions before trying to drop the database.

    Please try the following steps.

    First, run the following script on the master database.

        SELECT DB_NAME(database_id) AS DatabaseName, 'kill '+cast(session_id as varchar(10)), *
        FROM sys.dm_exec_sessions
        WHERE DB_NAME(database_id) NOT IN ('master')
        ORDER BY DatabaseName
    
    

    Second kill all sessions actively using the database with the output of the previous T-SQL script.

    Finally, try to drop the database from Synapse Studio.

    1 person found this answer helpful.