none
How to resolve the issue of a database that was in Recovery Pending mode

    Question

  • I am running Microsoft SQL Server 2012 on a Windows 7 Home Premium OS. I encountered a state of a database being in "Recovery Pending" mode. I am going to go over the details of how this happened. I was using SQL Server Configuration Manager to stop "SQL Server (MSSQLSERVER)" because I wanted to delete the file "C:\Temp\FileTableExampleDB_FileStreamFile". I was trying to get the following query to work:

    USE [master]
    GO
    ALTER DATABASE [AdventureWorks2012]
    ADD FILEGROUP [FileTableExampleDBFilestreamFG] CONTAINS FILESTREAM
    GO
    ALTER DATABASE [AdventureWorks2012]
    ADD FILE ( NAME = N'FileTableExampleDB_FilestreamFile',
    FILENAME = N'C:\Temp\FileTableExampleDB_FilestreamFile' ) TO FILEGROUP
    [FileTableExampleDBFilestreamFG]
    GO
    USE [AdventureWorks2012]
    GO
    IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1
    AND name = N'FileTableExampleDBFilestreamFG')
    ALTER DATABASE [AdventureWorks2012]
    MODIFY FILEGROUP [FileTableExampleDBFilestreamFG] DEFAULT
    GO
    USE [AdventureWorks2012]
    GO
    ALTER DATABASE [AdventureWorks2012] REMOVE FILE [FIleTableExampleDB_FilestreamFile]
    GO

    Previously, I ran the above query and encountered some errors when running the code. The file "C:\Temp\FileTableExampleDB_FileStreamFile" was created in the process. I wanted to delete "C:\Temp\FileTableExampleDB_FileStreamFile" because otherwise, the compiler would complain that "C:\Temp\FileTableExampleDB_FileStreamFile" was already created. I tried to delete this file manually, but I get an OS error that the file is being used by MSSQLSERVER. Therefore, I decided to stop MSSQLSERVER using SQL Server Configuration Manager. I did that, deleted the file successfully and restarted MSSQLSERVER. Unfortunately, the database AdventureWorks2012 changed its status to "Recovery Pending". Can you give me the easiest way to resolve this without deleting AdventureWorks2012? Thank you.

    Tuesday, July 31, 2012 2:00 AM

Answers

  • Fundamentally , this error is closely correlated to that Forcibly deletion process  of File stream file which I do think no other solution except waiting to finish recovery mode and either wise it will end with :

    • The most optimistic   probability  that it will end up with Online mode …So it will be fine and no need for any further action  ( Just you have to wait for a longer time if log file was such huge)..
    • The most pessimistic probability that it will end up with suspect  mode  ..SO it will be need to run the below process of DB Repair  but bear in mind that data loss might be there;

    Stop SQL Server and remove transaction log file of this DB then restart again where DB should go with suspect mode ….If so you can run the below query

    ALTER DATABASE [DB_Name] SET  SINGLE_USER WITH NO_WAIT

    ALTER DATABASE [DB_Name] SET EMERGENCY;

    DBCC checkdb ([DB_Name], REPAIR_ALLOW_DATA_LOSS  )

    ALTER DATABASE [DB_Name] SET online;

    ALTER DATABASE [DB_Name] SET  Multi_USER WITH NO_WAIT

    Kindly let me know if any further help is needed


    Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities


    • Edited by ShehapMVP Tuesday, July 31, 2012 7:52 AM
    • Marked as answer by CatGuy90 Tuesday, July 31, 2012 5:36 PM
    Tuesday, July 31, 2012 7:49 AM

All replies

  • Fundamentally , this error is closely correlated to that Forcibly deletion process  of File stream file which I do think no other solution except waiting to finish recovery mode and either wise it will end with :

    • The most optimistic   probability  that it will end up with Online mode …So it will be fine and no need for any further action  ( Just you have to wait for a longer time if log file was such huge)..
    • The most pessimistic probability that it will end up with suspect  mode  ..SO it will be need to run the below process of DB Repair  but bear in mind that data loss might be there;

    Stop SQL Server and remove transaction log file of this DB then restart again where DB should go with suspect mode ….If so you can run the below query

    ALTER DATABASE [DB_Name] SET  SINGLE_USER WITH NO_WAIT

    ALTER DATABASE [DB_Name] SET EMERGENCY;

    DBCC checkdb ([DB_Name], REPAIR_ALLOW_DATA_LOSS  )

    ALTER DATABASE [DB_Name] SET online;

    ALTER DATABASE [DB_Name] SET  Multi_USER WITH NO_WAIT

    Kindly let me know if any further help is needed


    Shehap (DB Consultant/DB Architect) Think More deeply of DB Stress Stabilities


    • Edited by ShehapMVP Tuesday, July 31, 2012 7:52 AM
    • Marked as answer by CatGuy90 Tuesday, July 31, 2012 5:36 PM
    Tuesday, July 31, 2012 7:49 AM
  • Are the transaction log files located in "MSSQL11.MSSQLSERVER\MSSQL\Log"? If so, what name do they share? For example, the files that I found started with "ERRORLOG", "FDLAUNCHERRORLOG", "log_", "SQLFT0000700005" and "system_health_0_". Why must I remove the transaction log files? Thank you.
    Tuesday, July 31, 2012 5:01 PM
  • I accidentally dropped the database AdventureWorks2012, but it is fine because it is just a test database. Thank you for your help.
    Tuesday, July 31, 2012 5:33 PM
  • Thanks a lot !!!

    It works for me. 

    Tuesday, February 12, 2013 8:59 AM