AzureSQL database gives me change tracking error

I just uploaded a SQL bacpac to AzureSQL.  This I have done a number of times.  Connected my service to the SQL database and tried to start the service.

This time I got an error.  Looking in Event Viewer I can see.

The following SQL error was unexpected:
  Change tracking is already enabled for database '2018-IS365'.
  ALTER DATABASE statement failed.
  SQL statement:
  IF NOT EXISTS (SELECT * FROM sys.change_tracking_databases WHERE database_id=DB_ID('2018-IS365')) ALTER DATABASE [2018-IS365] SET CHANGE_TRACKING = ON (CHANGE_RETENTION = 10 MINUTES, AUTO_CLEANUP = ON)

I looked into the SQL database, and sure enough there was a line in sys.change_tracking_databases table.  The problem was that in that table the [database_id] was equal to 48 while

SELECT db_id('2018-IS365')

resulted in 49.  Hence the error and my service tier failing to start.

To remove the change tracking from the database I executed (found here)

DECLARE @SQL NVARCHAR(MAX)='';
SELECT @SQL = @SQL + 'ALTER TABLE ' + s.name + '.[' + t.name + ']' +
 ' Disable Change_tracking;'
FROM sys.change_tracking_tables ct
JOIN sys.tables t
 ON ct.object_id= t.object_id
JOIN sys.schemas s
 ON t.schema_id= s.schema_id;
PRINT @SQL;
EXEC sp_executesql @SQL;

ALTER DATABASE [2018-IS365] SET CHANGE_TRACKING = OFF;

The service tier will take care of turning the change tracking on again when it starts.  You might need to repeat these steps if restarting the service tier.

According to Microsoft a fix is in the pipeline and likely ships in CU2.

Comment List
Related
Recommended