SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SQL Azure–Failed to Update because the database is read-only

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.


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.

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.


Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...