Azure SQL Restore stuck

Rodney Graham 61 Reputation points
2021-09-30T08:55:00.69+00:00

I have a 2gb DB that is stuck restoring for over 48 hrs.
It shows as an entry in SSMS but does not show up in the Azure Portal (see image attached)
I cant find any status or logs information in the Azure Portal either...

I have used restore successfully many times clearly something is wrong this time... I have tried use DROP db while connected to the master DB* in SSMS but SSMS says it cant even see the DB.

Any suggestions on what to do?

136586-image.png

*https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=sql-server-ver15

Azure SQL Database
Accepted answer
  1. Alberto Morillo 32,886 Reputation points MVP
    2021-09-30T14:26:12.54+00:00

    You can run the following query on the Master database of the Azure SQL Database server to track the progress of a restore. 

    SELECT major_resource_id, percent_complete
FROM sys.dm_operation_status 
WHERE operation LIKE '%DATABASE RESTORE%'

    If you don't see any restore then trigger a new one. If you don't see progress on the restore over time, create a support ticket.

    1 person found this answer helpful.
    0 comments

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rodney Graham 61 Reputation points
    2021-10-05T17:24:54.677+00:00

    Hi guys, thank so much for answering. I tried @Alberto Morillo recommendation but as he mentioned, no restore showed. I planned to then raise a ticket but could not action until the next day... and on returning to it... the restore had completed... 72+ hrs!!!

    I have no idea what made it run so long. I have done another one since and it was completed in 10 minutes. Azure Hiccup? Anyhow, if I encounter it again I will be following the above steps...

    1 person found this answer helpful.