Share via

Database stuck in restoring mode

  • Article

Question

Friday, January 27, 2017 8:16 PM

I tried restoring a .bak database in sql server 2014 using the GUI. It did not complete and hung indefintely.

No matter what I try, I can't get rid of this database so I can start anew.

I have tried several things like:

killing the process under activity manager

using some drop commands which fail due to locks.

I found a similar post on this site to list the locks for my database id, problem is I don't even see the database  using 

EXEC sp_databasesCan someone comment on what I can do to get rid of this. I haven't restarted the bloody server yet but I suspect that won't help either.Thanks!

All replies (7)

Friday, January 27, 2017 8:26 PM âś…Answered

Hi, if you haven't tried, possibly right click on the database and select delete.

Friday, January 27, 2017 8:59 PM

Try running EXEC sp_who2 and do a search for any SPID with a connection to the database (which there shouldn't be any if it is not online). If you find any run KILL <SPID>. Then a DROP DATABASE [<DB>] should drop the database from the instance, regardless if it is in restoring mode or not.

What error do you get when you try and run a DROP DATABASE command?

Joie Andrew "Since 1982"

Saturday, January 28, 2017 5:08 AM

Hi cspell,

run following command 

SELECT session_id as SPID, command, 
a.text AS Query, start_time, percent_complete, dateadd(second,estimated_completion_time/1000,
 getdate()) as estimated_completion_time 
FROM sys.dm_exec_requests
 r CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE')

Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

Saturday, January 28, 2017 12:50 PM

Hi, thanks for the help, see screenshot, query comes back empty.

Saturday, January 28, 2017 12:59 PM

As your drop database statement is hanging there is an active process ongoing on the database.

You are best to do this

alter database DatabaseName set single_user with rollback immediate

drop database DatabaseName

Now this too may fail in this case you will need to do something like this

declare @databasename sysname
select @databasename ='MyDatabaseName'
declare @spid int
declare @rowcount int
declare @str varchar(max)
select @str='kill @spid'
declare @newstr varchar(max)
select @spid=spid from sys.sysprocesses where dbid=db_id(@databasename) 
select @rowcount =@@ROWCOUNT
while @rowcount >1
begin
select @spid=spid from sys.sysprocesses where dbid=@databasename 
select @rowcount =@@ROWCOUNT
select @newstr=replace(@str,'@spid',@spid)
exec(@newstr)
end
alter database DatabaseName set single_user with rollback immediate
drop database DatabaseName

Saturday, January 28, 2017 1:04 PM

I did try this and it kept saying that there were open connections BUT, I tried the delete again, this time checking the "close existing connections"option in the UI (go figure) and that did the trick. Database was deleted! Thanks for the feedback everyone

Tuesday, January 31, 2017 12:17 PM

Please have a look on here it might be helpful for you: https://madhivanan.wordpress.com/2016/09/06/issue-in-recovering-a-database-that-is-in-the-restoring-state-reference/