question

Mhance avatar image
Mhance asked

SQL SERVER 2014 database stuck in Recovering

Have a database on SQL Server 2014 that was stuck in "Recovering" - let it run over the weekend & still not recovered. It was making the Server go unresponsive for the other databases today and want to try restoring from a backup from a week ago. The database would not go off line so I shut the SQL Server down, Renamed the .mdf & .log so the server could not find the files, started up SQL Server, then it let me take that database offline. Just waiting to make sure we don't have other problems. Questions is to get that database back - should be able to select Restore database & select the FULL backup and move it to a new disk while it is still OFFLINE? This is a LARGE database 444GB and want to move it to another drive on the server than whet the database backup was originally was done on.
databaserecovery
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
JohnSterrett avatar image
JohnSterrett answered
You should be able to restore from backup and move the database files to a new path. Was there any chance your restore command used "WITH NORECOVERY"? If so, your database would be left in recovering until you brought it online. Did the error logs show any progress on recovering? Any idea how many VLFs you have with this database?
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

That is what I'm going to try - thought that it should work that way - just have to remove the old files first. The server went unresponsive and the Server was rebooted. When the databases came back up this database came back as recovering. I may have horked that recovery by removing the backups & log backups to another server - not really positive if it needed those or not. I won't be doing that in the future - just have to figure out a way to get space on the server some other way. The database never had the option to bring online - when right clicked in Management Studio just had Take Offline - when I tried that it didn't work said it was Recovering. The logs never showed any progress on recovering - even after I moved those log file back. Do not know how many VLFs there are - but the .ldf is 292GB. THe user says he did something he should not have done to make the log fill up - but this one is always large. I will be looking into the VLFs
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.