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.
2,884 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 26,131 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.