SQL Azure–Failed to Update because the database is read-only
Friday, October 5, 2012
An error message for one of our many SQL Azure databases was brought to my attention today.
Msg 40628, Level 16, State 1, Line 1
Failed to update database 'UAT_US_SS02' because the database is read-only. Please contact your Azure service owner. There may be billing related issues with your Azure account.
While I appreciate Microsoft’s attempt to steer people towards the root cause of a problem, but in this case, the error had nothing to do with billing. A quick check of the database size confirmed that it was only 426MB in total size for this 5 GB Web Edition provisioned database.
SELECT (SUM(reserved\_page\_count)*8192)/1024000 AS [SizeinMB] FROM sys.dm\_db\_partition_stats
None the less, I attempted to alter the edition, and size of the database, more to see what would happen.
ALTER DATABASE [uat-us-ss02] MODIFY (EDITION='BUSINESS', MAXSIZE=100GB) Failed to update database 'UAT\_US\_SS02' because the database is a continuous copy target.
The database in question had been restored from another database using the DBCopy command. I started to get the feeling that the copy operation while looking successful, some internal state on SQL Azure was incorrect.
A review of the database in the sys.databases system view, showed it as online, and all properties (aside from Name) were the same as other functional databases.
select * from sys.databases order by name
Next I reviewed the sys.dm_database_copies table and noticed that there was a pending copy operation which had been running for over 3 hours.
select * from [sys].[dm\_database\_copies] c inner join sys.databases db on c.database\_id = db.database\_id
We dropped the database and waited an hour in hopes that SQL Azure would clean up after itself (and clean-up after us too). Thankfully that seemed to do the trick, and we avoided having to open a support ticket. It turns out this database had been dropped/recreated several times in quick succession before I was brought in. This DB was also one of several databases created from the same source DB and nearly the same time (User Acceptance Testing Environment). Somewhere along the way SQL Azure seems to have gotten confused.