How to delete a SQL database?

Question

I created a SQL database with service tier GP_S_Gen5_4, and it was supposed to shutdown automately when there is not connection. But recently, this database is always online even I didn't connect it for a long time. Also, I couldn't delete it. I wonder if there is anything wrong with the database configuration.

Answer 1

Hi 乐瑶 刘,

Welcome to Microsoft Q&A forum.

As I understand, you are trying to delete a SQL Database.

If the Azure SQL Database is Serverless, then it is a compute tier for single databases in Azure SQL Database that automatically pauses databases during inactive periods when only storage is billed and automatically resumes databases when activity returns. 

If auto-pausing is enabled, but a database does not auto-pause after the delay period, the application or user sessions may be preventing auto-pausing. To see if there are any application or user sessions currently connected to the database, connect to the database using any client tool and execute the following query:

SELECT session_id,
       host_name,
       program_name,
       client_interface_name,
       login_name,
       status,
       login_time,
       last_request_start_time,
       last_request_end_time
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_resource_governor_workload_groups AS wg
ON s.group_id = wg.group_id
WHERE s.session_id <> @@SPID
      AND
      (
      (
      wg.name like 'UserPrimaryGroup.DB%'
      AND
      TRY_CAST(RIGHT(wg.name, LEN(wg.name) - LEN('UserPrimaryGroup.DB') - 2) AS int) = DB_ID()
      )
      OR
      wg.name = 'DACGroup'
      );

Let me know if this helps or you have a different ask.Thanks

Answer 2

Hi @乐瑶 刘
Auto-pausing is triggered if all of the following conditions are true during the auto-pause delay:

·        Number of sessions = 0

·        CPU = 0 for user workload running in the user resource pool

And to answer your question- How to delete a SQL database?-

You can delete from the SQL Server Management studio or from the Azure portal.

-- Azure SQL Database, Syntax

DROP DATABASE database_name [;]

From portal, you can click on Delete, specify the database name.

If you encounter any error, please share error message or share the screenshot.

Thank You!