Azure SQL Database – Failed to Delete Database Nightmare

After demo building and testing features out within Azure SQL Database I like to delete the databases to save costs. Very rarely the following happens when trying to delete a database within Azure.

The Error

failedtodel

Failed to delete the database: TestDB. ErrorCode: 400 ErrorMessage: Database ‘TestDB’ cannot be deleted because it is used as a sync metadata database which still contains sync groups and/or sync agents.

After some digging around I remembered that I tried to setup a data sync group where this database was involved in bi-directional synchronization with another Azure SQL Database. The other database was deleted a long time ago and clicking on the sync option within the portal confirmed that nothing was actually ever connected or ready.

SyncGroup

So I connected to the database via SSMS (SQL Server Management Studio) and had a look around, quite clearly there are sync based objects within my database with the tables naming convention as DataSync.XXXX, DSS.XXXX, TaskHosting.XXXX

ssmsazuresync

Also many DSS. Stored procedures.

spsync

Also many scalar functions.

syncfunctions

Documentation for this feature is not very detailed and there is no section on “cleaning up” / removing sync groups (even though it is simple). This being a test server I decided (rash decision) to try removing the tables first which proved to be the wrong decision (see later). To remove data sync groups all you need to do is click into the group and delete it which we will see later on. Yes, I admit, I made a bad mistake here.

So I repeat, to cleanly remove sync groups you: Click into the group and delete it…

Anyways, this is what I did before knowing about the correct and simple way. Below is a list of the tables involved with this feature which were deleted.

SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']'
AS SchemaTable
FROM sys.tables
WHERE schema_id  1

letables

 DROP TABLE [TaskHosting].[MetaInformation]
DROP TABLE [TaskHosting].[Job]
DROP TABLE [TaskHosting].[MessageQueue]
DROP TABLE [TaskHosting].[Schedule]
DROP TABLE [TaskHosting].[ScheduleTask]
DROP TABLE [DataSync].[schema_info_dss]
DROP TABLE [DataSync].[scope_info_dss]
DROP TABLE [DataSync].[scope_config_dss]
DROP TABLE [dss].[subscription]
DROP TABLE [DataSync].[provision_marker_dss]
DROP TABLE [dss].[task]
DROP TABLE [dss].[userdatabase]
DROP TABLE [dss].[scaleunitlimits]
DROP TABLE [dss].[ScheduleTask]
DROP TABLE [dss].[taskdependency]
DROP TABLE [dss].[syncgroup]
DROP TABLE [dss].[agent_instance]
DROP TABLE [dss].[UIHistory]
DROP TABLE [dss].[EnumType]
DROP TABLE [dss].[action]
DROP TABLE [dss].[MetaInformation]
DROP TABLE [dss].[agent_version]
DROP TABLE [dss].[agent]
DROP TABLE [dss].[configuration]
DROP TABLE [dss].[syncgroupmember]

I Broke The Portal

At this point I really confused the portal. I re-produced the issue (by doing the above) and got the below screen which I faced in real life. It was stuck at a loading state and I needed access to it to delete the group.

loading

So I used point in time restore to recover the database where then I moved the data back into the original Azure SQL Database (Yes – the import/export wizard works great with Azure SQL Databases).

restorepoint

Below is the data movement into the “broken” database.

copydata

I now have a fully functional sync screen where I can click into the group and agent to CLEANLY REMOVE IT!

Removing Sync Groups – The right way

The common message from this post? How to properly remove sync groups.  Click into the group where then you will be given a delete option.

remove
confirmDele

The outcome you should now get.

delCorrect

Morale of this post – Do not “jump the gun” and do something as rash as deleting tables /objects if you do not know / understand the ramifications of such an action. I have truly learnt a lesson here. I thought I would share this mistake, do not judge me too harshly and I am very thankful for Azure’s Built-in backups!

Thanks for reading.

 

 

2 thoughts on “Azure SQL Database – Failed to Delete Database Nightmare

  1. Pingback: Dew Drop - April 4, 2018 (#2698) - Morning Dew

  2. Pingback: “Failed To Delete Database” In Azure – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s