4

SQL Server database which is very large (4TB) is stuck in recovery state. Reason: Data center had a planned downtime and we had to turn the physical machine off and when it turned on, as I read in error logs, SQL Server couldn't access the files, because files were on a SAN machine, and probably things didn't went smooth.

FCB::Open failed: Could not open file E:\SomeFolder\SomeDatabase.mdf for file number 1. OS error: 5(Access is denied.).

Now that I've made sure that SAN is up and running, and permission are OK, how can I tell SQL Server to continue recovering? Since this database is very large, I don't want to interrupt the course of recovering and start from the beginning. And also, any backup would take hours to complete.

2
  • This is an OS-level error. SQL Server cannot just decide to continue. You can't tell it to continue. You need to ensure OS-level permissions. Grand full control to Everyone to test this. Then revert to saner permissions.
    – usr
    Jan 17 '16 at 13:10
  • If the permissions are ok, then try doing DB OFFLINE and DB ONLINE.
    – SQLPRODDBA
    May 20 '16 at 7:51
2

SQL server service account does not have permission to access the file.

  1. Navigate to the folder in which this file is residing
  2. Grant Full control privilege on this folder to the SQL server service account.
  3. Bring the DB Offline - Online or Restart SQL server services.
0

I'm not an SQL internals expert, but I highly suspect that there will be nothing you can do to gently prompt SQL to re-check the files and continue database recovery where it left off; it will have abandoned the recovery effort when encountering that error.

As the other answer suggests, you'll have to either restart the SQL instance entirely, or take the database offline and back online to start recovery over.

0

Now that I've made sure that SAN is up and running, and permission are OK, how can I tell SQL Server to continue recovering?

If your database is in a "Recovery Pending" state, you simply try to bring the DB online:

ALTER DATABASE [db_name] SET ONLINE
GO

This will restart the recovery process.

Note: For a VLDB, this recovery process can take a long time.

Since this database is very large, I don't want to interrupt the course of recovering and start from the beginning. And also, any backup would take hours to complete.

Recovery is not running if the DB failed with the Access Denied error you mentioned, so there is nothing to interrupt. You can verify this using this query:

SELECT command AS [Command],
    DB_NAME(database_id) AS [DB],
    CAST(percent_complete AS DECIMAL(5,2)) AS [PercentComplete], 
    DATEADD(SECOND, estimated_completion_time, GETDATE()) AS [ETA],
    wait_resource, 
    wait_time, 
    wait_type, 
    blocking_session_id
FROM sys.dm_exec_requests
WHERE command = 'DB STARTUP' -- may need to change this if troubleshooting recovery as part of attach database or restore

Note: If you run this query on an AG secondary, you will see multiple sessions running DB STARTUP.

Note 2: This query isn't 100% accurate RE: ETA and % complete but at least gives you an idea that the process is running.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.