SQL Server 2005 Restore Hanging

Question

I have a database showing up in SQL Enterprise Manager as "(Restoring...)"

If i do SP_WHO there is no restore process.

The disk and CPU activity on the server is very low

I think it is not restoring at all.

How can I get rid of this?

I've tried renaming the underlying MDF file, but even when I do "NET STOP MSSQLSERVER" it tells me the file is open.

I've tried using PROCEXP to find what process has the file open, but even the latest PROCEXP can't seem to do that on Windows Server 2003 R2 x64. The lower pane view is blank.

In the SQL Server log it says "the database is marked RESTORING and is in a state that does not allow recovery to be run"

Answer 1

If you are trying to get rid of the lock on the file I would recommend getting Unlocker http://ccollomb.free.fr/unlocker/

It'll give you an option to unlock the file, or kill the process that has locked the file. Run this on the mdf and ldf files.

Another option is to try to Detach the files from Enterprise Manager or Sql Management Studio and then reattach the db. You can try this before running unlocker to see if sql server will just release the mdf and ldf files.

CAUTION: If you kill the process you might lose data or the data might get corrupted so use this only if you are trying to get rid of it and you have a good and tested backup.

Answer 2

Did you restore it or did someone else restore it?

Any errors or messages in the SQL error log file?

If you query sys.dm_exec_requests, you should see a progress. What is it?