Azure Serverless - temp databases created and cannot drop them...

Question

Can see some temp databases created auto , but on tryign to delete them, get an error that they are in use. Have tried deleting from studio, using sql code but still same error. how to find what dependencies are there and how to delete them.

Answer 1

@Neha Vageriya - Thanks for the question and using MS Q&A platform.

If you are unable to drop a temporary database in Azure Synapse Serverless, it is likely that there are active connections or dependencies on the database that are preventing it from being dropped. To identify the dependencies and connections, you can try the following steps:

Connect to the master database in Azure Synapse Serverless using SQL Server Management Studio or Azure Synapse Studio.

Run the following query to identify the active connections to the temporary database:

SELECT * FROM sys.dm_exec_sessions WHERE database_id = DB_ID('tempdb')

This query will return a list of active sessions that are connected to the temporary database.

If there are active connections, you can try killing the sessions using the following query:

KILL <session_id>

Replace <session_id> with the ID of the session that you want to kill.

Once all the active connections are terminated, you can try dropping the temporary database using the following query:

DROP DATABASE <database_name>

Replace <database_name> with the name of the temporary database that you want to drop.

If you are still unable to drop the temporary database, it is possible that there are dependencies on the database that are preventing it from being dropped. In this case, you can try disabling the dependencies using the following query:

ALTER DATABASE <database_name> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

This query will set the database to single-user mode and roll back any active transactions. Once the database is in single-user mode, you can try dropping it using the DROP DATABASE command.

For more details, refer to the SO thread addressing similar issue: https://stackoverflow.com/questions/65384603/drop-database-error-for-azure-synapse-database

Hope this helps. Do let us know if you any further queries.