Rory PS on Tue, 19 Feb 2013 23:31:40
I tried to create a copy of a database X that is approx 1.5GB in size, using SQL like "create database [bak_X] as copy of [X]". This copy operation which normally takes about 15 mins has been executing since 1am on 2013-02-16, i.e. it's been going for more than 10 days, so I assume it is never going to finish. The database 'bak_X' that is partially created by the copy is stuck in the Copying state and I can't work out how to get rid of it.
When I try to drop the database from the management portal I get an error:
Deleting database bak_ps-sr-test-1-db failed
The server encountered an internal error. Please retry the request.
When I try to drop it from SQL Management Studio I get the error:
Msg 3702, Level 16, State 1, Line 1
Cannot drop database "bak_X" because it is currently in use.
I don't want to do anything to the source database because I have systems running against it.
Mike Lewis (SQLTuna) on Wed, 20 Feb 2013 09:17:01
Unforunately we don't have the luxury of killing processes in SQL Database, so to that end I would recommend raising a ticket with Microsoft. I suspect the copy would finish, eventually, but there are so many abstractions in Azure that it makes it very difficult to know what the cause of the delay is.
Try interrogating a few DMVs first though to see if there's an obvious cause of the hold up, such as dm_exec_query_memory_grants or dm_exec_requests.
Rory PS on Tue, 26 Feb 2013 13:54:49
It has now been copying for 10 days.... those views indicate there's nothing else running against the source database. I can't connect to the target database because it's in the Copying state.
Azure Billing Support have been unhelpful at answering whether I'm being charged for this database that I'm unable to use and unable to delete. Presumably I am being charged though.
Any comments from MS? Anyone else had Copying databases they can't access or delete that never finish copying?