Recovery pending sql как исправить

Ручной Способ исправить Состояние ожидания восстановления SQL Server

Люди, которые использовали SQL-сервер, возможно, однажды столкнулись с ошибкой SQL база данных в состояние ожидания восстановления из-за ее повторного появления. Если вы не знаете, почему происходит эта ошибка, значит, ваш раздел базы данных, вероятно, заполнен. SQL не может открыть базу данных и не может заблокировать файл базы данных, который очень похож на базу данных в автономном режиме. Это больше похоже на то, что что-то мешает запуску сервера. В этом блоге мы собираемся обсудить, как исправить состояние ожидания восстановления SQL Server с помощью наилучших возможных методов. Прежде чем продолжить, давайте узнаем, каковы причины этой ошибки.

Причины – Состояние Ожидания восстановления базы данных SQL Server

• Раздел базы данных переполнен или недостаточно памяти
• Аппаратный сбой
• Повреждение в лог-файлах
• Из-за какой-либо незавершенной задачи или при закрытии были некоторые действия, ожидающие со стороны пользователя
• Повреждение или повреждение в файлах MDF
• Запуск сервера без промежутков времени

Мгновенное Решение: Используйте средство восстановления SQL SysTools, чтобы исправить состояние ожидания восстановления в базе данных SQL Server. Это программное обеспечение может быстро устранить все ошибки, связанные с базой данных SQL. После восстановления он предоставляет возможность экспортировать данные в базу данных SQL или сценарии SQL.

Ручные способы исправить Состояние ожидания восстановления SQL Server

Как всегда можно увидеть или испытать, что ручные способы довольно сложны и опасны в использовании. Поэтому, прежде чем запустить его, убедитесь, что у вас есть резервная копия базы данных. Если вы новичок в этом, то рекомендуется, чтобы вы выполняли его под руководством технического специалиста или не выполняли его.

Способ 1

В этом ручном методе для разрешения Состояние ожидания восстановления базы данных SQL Server необходимо запустить принудительное восстановление.

1. Запустите нижеуказанные SQL-запросы.

ALTER DATABASE (Database Name) SET EMERGENCY;

ALTER DATABASE (Database Name) set single_user

DBCC CHECKDB ([Database Name], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

ALTER DATABASE (Database Name) set multi_user

2. База данных помечена как READ_ONLY в аварийном режиме, отключает ведение журнала и предоставляет доступ только системным администраторам.

3. После того, как эти шаги будут выполнены, повреждение файла будет исправлено, и база данных снова будет подключена автоматически.

Способ 2

В этом втором методе, который может разрешить состояние ожидания восстановления в базе данных SQL Server, нам нужно сначала поработать в аварийном режиме, давайте посмотрим, как.

1. Выполните следующие команды:

ALTER DATABASE (Database Name) SET EMERGENCY;

ALTER DATABASE (Database Name) set multi_user

EXEC sp_detach_ db ‘(Database Name)’

EXEC sp_attach_single_file_db @Database Name = ‘(Database Name)’, @physname = N’(mdf path)’

2. Система автоматически удалять поврежденные журналы и создаст новый.

Если вы успешно выполнили оба метода, то до сих пор проблема Состояние ожидания восстановления базы данных SQL Server может быть решена. Если нет, то рекомендуется перейти на более безопасный и лучший подход, который является автоматизированным методом.

Автоматизированное решение для исправления Состояние Ожидания восстановления SQL Server

Автоматизация гораздо лучше, чем тратить много времени и усилий на ручные методы. Для автоматического метода вы можете перейти к Восстановление базы данных SQL. Это программное обеспечение помогает восстановить поврежденный файл MDF со всеми объектами базы данных. Для выполнения процесса восстановления базы данных SQL не требуется резервное копирование. Можно легко восстановить базу данных SQL без резервного копирования. Давайте узнаем, как это работает для восстановления поврежденных файлов базы данных SQL и устранения состояние ожидания восстановления в базе данных SQL Server.

1. Установите и запустите программу восстановления SQL, затем нажмите кнопку «Открыть», чтобы загрузить файл базы данных.

2. Выберите режим быстрого или расширенного сканирования, а затем установите флажок Автоопределение версии файла SQL Server.

3. Теперь начнется процесс сканирования. После сканирования вы можете увидеть предварительный просмотр восстановленных предметов.

4. Выберите опцию «Экспорт» сверху и выберите «Экспорт данных в базу данных SQL или сценарии SQL».

5. Заполните все необходимые данные ниже и выберите экспорт только со схемой или только со схемой и данными.

6. В конце нажмите кнопку «Экспорт», чтобы восстановить файлы базы данных SQL.

Вывод

Как мы уже обсуждали в блоге, насколько важна для нас база данных SQL и насколько она склонна к состояние ожидания восстановления SQL Server , вы должны знать эти методы для спасения. Но как мы видим, насколько сложен ручной метод, поэтому всегда рекомендуется переходить на автоматизированный метод. Это лучший инструмент SQL для восстановления поврежденной базы данных SQL и устранения проблемы SQL база данных в состояние ожидания восстановления.

How to fix Recovery Pending State in SQL Server Database?

How to fix Recovery Pending State in SQL Server Database?

9 Answers 9

Execute the following set of queries:

When your Database .mdf file name is renamed, this issue is occurred. To solve:

Restart SQL EXPRESS in Services, Pending issue is solved.

In our case it was caused by the disk drive running out of space. We deleted some junk to free space, then fixed the "Recovery Pending" by stopping and restarting the SQL Server Service.

Detach, re-attach, solved !

While using SQL Management Studio, there is an intermittent issue when a user is changing the Database Names then sometimes SQL Server uses the same DB file for two different Databases. If SQL Server is in this state then you would be probably seeing the following error if you try Mahesh’s answer:

«The process cannot access the file because it is being used by another process»

To fix this issue:

1. Stop MSSQLServer service from the Services Console MMC
2. Rename the DB and the Log files (Database Properties -> Files)
3. In the Object Explorer window in SQL Management Studio, refresh the ‘Databases Folder’, if you see that there is another Database node (in addition to the one which you are trying to rectify this issue) which is shown in ‘Recovery Pending State’ then proceed to the next step. If you do not see this error then you need to try something else to resolve this issue
4. Make a backup of the DB and Log files for new offending node from step 3 and !!Careful!! delete the database
5. Restore the Db File names which you changed in Step 2
6. Now start the MSSQLServer service from the Services Console
7. Re-try the steps from Mahesh’s answer

In my case, this affected the secondary server in a High Availability SQL Server cluster. The primary was Synchronizing but the secondary was Recovery Pending .

After checking in cluadmin.msc , realised that the secondary server wasn’t healthy in the cluster. Then determined Cluster Service had failed to start on the second cluster box after a Windows Update enforced reboot (may have happened because the file share witness was rebooting after a similar Windows Update at the same time).

Starting the Cluster Service brought the databases back into Synchronizing status.

Ensure that the "Log On" account for the "SQL Server (201x)" service (listed in Windows Services (Manager)) has sufficient rights. You may try changing it to another Logon. In my case, changing it from "This account" to "Local System account", restarting the "SQL Server (xxxx)" service and SQL Server Management Studio (SSMS), and logging into SSMS again resolved the issue.

Background (in my particular case): I had 3 different instances of SQL (2008r2, 2012 and 2014) running on my local PC, and was busy moving a folder (which I later discovered contained some SQL data & log database files) to another PC. Halfway through, I stopped the SQL Services in Service (Manager), hoping that the files would move across without issues — since they would now, no longer be in use. I realized that I needed to confirm the database names, and file locations in SQL (in order to set them up again on the new pc), so I copied the SQL data and log files back (to the original locations). After restarting the PC — the majority of the databases on various instances all showed as: "Recovery Pending" in SSMS. After first trying the procedure from stellarinfo (listed as an answer here by @Mahesh Thorat) on 2 of the databases, and still not having any luck, a post SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location on Pinal Dave’s SQL Authority website and the post: Operating System error 5(Access is Denied) on SQL Server Central gave me an idea that it could be rights related after looking at the SQL Errorlog and finding "Operating system error 5: "5(Access is denied.)". Another post Msg 3201, Level 16 Cannot open backup device. Operating system error 5(Access is denied.) at SqlBak Blog seems to support this.

How To Fix the SQL Server Recovery Pending Error

What causes a SQL Server recovery pending state?

If your database is stuck in a ‘recovery pending’ state, there are a few things that could have caused this.

• Sudden power failure. Loss of power can leave the database in a recovery state if data was being written to a row. This sudden loss of connectivity can cause database issues if data was practically written and then interrupted.
• Hardware failure. Similar to power failure, this causes an improper shutdown of the live database that often leaves queries and modifications in a broken, half-completed state.
• Lack of memory. SQL servers can utilize caching and buffer pools to operate on limited memory. However, if memory resources are stretched too thin, this can cause problems in the database file and result in a ‘recovery pending’ error.
• Log file corruption. Corruption in the log file is a common cause for a pending recovery status. Unexpected shutdowns, faulty memory, and limited storage can cause issues with the transaction log file that result in a pending state.

There are a number of different types of states a database can be in. Heavily damaged databases are normally found in numerous inconsistent states, where the primary files of the database are unable to function.

Even if your SQL database is stuck in a ‘recovery pending’ state, it’s important to know the additional states in case it changes during the repair process. The primary states of a database are as follows:

• ONLINE – This is the normal healthy state of the database and indicates that files are accessible to users.
• OFFLINE – The database is inaccessible and offline due to action taken by the user. The database will remain offline until it is manually brought back online.
• SUSPECT – The primary filegroup of the database may be damaged, and could not be recovered during startup. This leaves the database in an unavailable state.
• RECOVERING – The database is in the process of recovering. If successful, the database will return to an online state and become accessible. If this process fails, the database will return to a suspect state and remain unavailable.
• EMERGENCY – When set to emergency, the database is put into a single-user mode where access is limited to read-only, and accessible to only accounts that belong to the sysadmin role. This state is primarily used for troubleshooting and manual recovery processes.
• RECOVERY PENDING – The server has encountered an error during the recovery process. Missing files or system resource issues could be preventing the recovery from completing. The database is unavailable in this state.

If you’re having problems verifying that the state of your database has changed, you can check the current state by selecting state description (state_desc) while in catalog view, or check the Status property under the DATABASEPROPERTYEX function.

How to fix SQL database recovery pending

There are a few ways we can attempt to identify and fix what is causing the recovery pending error. Before making any changes to the database make sure you take a complete backup and move it to another server.

Running A Forceful Repair

The steps below will force the database into an EMERGENCY state and force a repair command. If successful, this will bring the database into an accessible state and automatically transition out of EMERGENCY mode.

Reconnecting The Main Database

The next method disconnects the database and then reattaches it again. This can help resolve SQL recovery pending issues as well as provide a clean log file if the current log is corrupted or inaccessible. If the steps above didn’t work, try these next.

Using SysTools SQL Database Recovery

If both manual methods do not recover the database from its pending recovery state, the next best option is to leverage the aid of a SQL recovery tool. In this example, we’ll be using SysTools SQL Database Recovery.

SysTools supports almost all versions of SQL server and features a handy preview tool that allows you to browse both the corrupted and health elements of your database file without being mounted to the SQL environment.

You can download and use the tool for free to see what data is considered recoverable.

Even when using recovery tools it’s important to ensure you have an additional backup of the data in case anything goes wrong.

Download Systools SQL Database Recovery

Navigate to the SysTools SQL Recovery download page and download the software. You can go through all of the steps with the free version, however, you will not be able to actually recover the data unless you purchase the software. The software offers a 30-day refund, but you should ensure your data is actually recoverable using the demo version first.

After running the executable and launching the software a simple main screen displays. Here you can view your corrupted and recovered data, import and export database files, and navigate the database structure.

Under the Open tab, navigate to the corrupted MDF file and import it into the program. You’ll have the option to choose a scanning mode. For most cases, Quick Scan will be able to identify and repair most issues. If this is not the case an

Advanced Scan can be done. This may take significantly longer spending on the size of your database objects. Next, choose your version of SQL Server below, or check the “Auto detect SQL server” box. This will quickly scan the file and associate it with its proper server environment.

If you have secondary NDF files that are included with the database you’ll have the option to include them as well. There is an additional checkbox to recover deleted items. Only check this if you’re looking to recover accidentally deleted objects.

After clicking OK a scan will start on the file. Systools begins identifying problems and detecting the recovery pending status error. You can keep tabs on the progress through the interface and even see how many tables, rules, triggers, and functions it has identified.

A notification will appear upon completion letting you know the scan is finished. It prompts you to run an advanced scan if you’re still missing data or experiencing the recovered pending status. The data will immediately be visible giving you the option to check through the tables and objects.

If you still see problems with your data or evidence of corruption, restart the process, and run an Advanced Scan. If the data looks good you’ll be able to export it as either a SQL Database or as a server compatible script.

Exporting As A Database

Choose the SQL Server Database option under the Export tab. Enter your server name if it isn’t detected automatically, and then provide a method for authentication if that is needed for your database file. You can choose to authenticate with domain Windows credentials or SQL Server Authentication.

Choose the option Create New Database and give it a name. Once again you’ll be able to preview and navigate the database file within the recovery windows. There are also additional options to export with schema and data, or only schema. Lastly, you can tell Systools whether or not you want to include any objects and records that were recovered from deleted objects.

Alternatively, you can follow the same steps but choose to export this to an existing database that overwrites the corrupted data.

Next, let’s specify the Collation Settings you want to use for the data, and check the preview box below to ensure it looks correct. When you’re ready, click Export to start the process. The status of the exported records will appear and list out the record count of all table names alongside their status.

Once completed you can save the export details as a CSV file and quickly open it to double-check that the tables and data are correct. This is a nice feature if you’re not quite ready to launch SQL Server.

Exporting As A Server Compatible Script

Files can also be saved and exported as a .SQL server-compatible script. Choose the SQL Scripts option under the Export tab and use the file navigator to select the parts of the database you wish to recover.

After choosing your recovery options you’ll be prompted to pick an export path. When the export is completed you’ll receive a notification along with steps detailing how to recover from your script.

Follow these steps to recover your database from the script:

1. Run the UserDefinedDataTypes.sql. This creates the exported database
2. Run every TableName.sql file in the export. This generates each table and it’s records.
3. Execute Stored_Procedure.sql and Views.sql.

Once completed you can run the database batch file in command prompt using the following syntax.

If you’re still struggling to find a tool that works for you, be sure to review our seven best SQL recovery tools for further assistance.

How to avoid server recovery pending errors

Hopefully, at this stage, you’ve been able to restore your files and get your database back into an online state. Let’s review some steps you can take to avoid having your database enter a recovery pending state again.

Ensure your host is on a battery backup unit. If power were to fluctuate or fail completely, you want your server to have enough power to stay on long enough to be shut down properly. While this isn’t ideal, it does help avoid sudden corruption when the database is interrupted in the middle of serving a query. A better but more expensive alternative is to have a separate power source such as a backup generator.

Monitor your SQL environment for troublesome events. Reacting to events such as low disk space and maxed memory is never ideal. By using tools such as Manage Engine Application Monitor you can configure monitors to alert on creeping disk space, expanding log files, and overall resource utilization. This not only monitors the physical environment but also helps avoid the database from entering a recovered pending state due to lack of resources.

Backup and test your database regularly. Incremental backups help you recover from dropped tables and sudden corruption that can happen with large databases. Ensure you’re both backing up and testing to eliminate downtime and give yourself peace of mind.

SQL Server Recovery Pending FAQs

Why does SQL Server say recovery pending?

In SQL Server, the Recovery Pending message means that a necessary restore of the database is blocked, awaiting the availability of a resource or waiting for an object lock in the target database to be released. This doesn’t mean that the recovery has been aborted, just that it is waiting.

What causes SQL database in recovery mode?

There are many reasons why an SQL Server recovery can get stuck. The three most common are:

1. An uncommitted transaction during a server crash
2. The database file has become corrupted
3. The transaction log has hit its configuration size limit

Why does a database go into suspect mode?

Data goes into suspect mode if there is a problem with the primary filegroup. A database in this mode cannot be recovered by the regular processes of the DBMS.

How to fix Recovery Pending State in SQL Server Database?

Summary: This post will outline the reasons why a database (db) is marked in recovery pending state. Also, it will describe methods to fix ‘SQL server database in recovery pending state’ problem. You can resolve the problem by executing queries in SQL Server Management Studio (SSMS), or by using Stellar Repair for MS SQL software.

SQL Server Database States

A SQL database is considered to be damaged if one or more of its core files are in an inconsistent state. Depending on how severe the damage is, the db is marked with different states. Some of these states are:

• Online – If one of the data files is damaged when executing a query or some other operation, the database will remain online and accessible.
• Suspect – If a database cannot be recovered during startup of SQL Server, the database is marked as Suspect.
• Recovery Pending – If the SQL Server knows that database recovery needs to be run but something is preventing it from starting, the Server marks the db in ‘Recovery Pending’ state. This is different from the SUSPECT state because it cannot be said that recovery is going to fail – it just hasn’t started yet.

Figure 1- Database marked in ‘Recovery Pending’ state

Let’s first understand how you can check the current state of the database.

How to Check Database State?

To check the current state of an SQL db, run the following query:

Executing the query will give you an output that looks something like:

Figure 2- Database States

Reasons behind Recovery Pending State in SQL Server

Some of the reasons causing such an issue are:

• The database didn’t shut down properly and there is at least one uncommitted transaction active during the shutdown, resulting in deletion of the active transaction log file.
• User tried moving the log files to a new drive to overcome server performance issues but ended up corrupting the log files in the process.
• Database Recovery cannot be initiated due to insufficient memory space or disk storage.

Methods to Fix Recovery Pending in SQL Server Database Issue

Note: Before initiating any of the following repair procedures, make sure to take db backup, so as to have a fail-safe copy in case anything goes wrong.

Method 1 – Manual Ways to Resolve the Database in Pending State

Following are the two manual ways that helps bring SQL database in recovery mode to normal mode:

1. Mark Database in Emergency Mode and Initiate Forceful Repair

Database EMERGENCY mode marks the database as READ_ONLY, disables logging, and grants access only to system administrators. Essentially, setting the db in this mode can bring the inaccessible database online.

Note: Usually a database comes out of EMERGENCY mode automatically. If you are having issues restoring the db from this mode, read this Recover SQL Database from Emergency Mode to Normal Mode.

Once you have opened the db in EMERGENCY mode, try repairing the database using the DBCC CHECKDB command with the ‘REPAIR_ALLOW_DATA_LOSS’ option. To do so, open SSMS and execute the following set of queries:

2. Mark Database in Emergency Mode, Detach the Main Database and Re-attach It

This solution also requires to mark db in EMERGENCY mode. Once done, take the database offline (detach) and then bring it online (re-attach). To do so, execute the following set of queries in SSMS:

The above set of queries help make the server get rid of the corrupt log and build a new one automatically.

Method 2 – Use Stellar Repair for MS SQL Software

If the above methods cannot help resolve the db in recovery pending state issue, use Stellar Repair for MS SQL software. The software can help you repair corrupt database files and bring the database back online from recovery pending state quickly and smoothly.

• Download, install and Run Stellar Repair for MS SQL software.
• From Select Database window, click Browse or Search to select the database file you want to repair.

Figure 3 – Select Database File

• Software provides Standard Scan and Advanced Scan option to scan the corrupt database.

Figure 4 – Scan Mode

• Click Repair to start the repair process.
• The software shows preview of all the recoverable db components.

• To save the repaired file and its components, click Save on File menu.

Figure 6: Save option

• From the Save Database window, do the following:

1. Choose MDF under Save As.
2. Select New Database or Live Database under Saving Option.
3. Enter the required details in the Connect to Server section by Authentication or SQL Server Authentication.

Figure 7: Authentication options for connectivity

• Click Browse to choose the destination for saving the repaired file.

Figure 8 – File Saving Options

• Click Save.
• When the ‘Save Complete’ message box appears, click OK.

Figure 9 – Save Complete Window

The repaired file will get saved in the selected location.

Note: For detailed information about using Stellar Repair for MS SQL software, refer to this user guide.

Key Features of Stellar Repair for MS SQL

• Repairs corrupt and inaccessible MS SQL Database (.mdf and .ndf) files.
• Recovers all database components like tables, keys, indexes, triggers, rules, and stored procedures, etc.
• Recovers deleted records.
• Supports MS SQL 2019, 2017, 2016, 2014, 2012, 2008, 2008 R2 and all lower versions.
• The software is trusted by Microsoft MVPs.

Conclusion

This post outlined reasons behind ‘db in recovery pending state’ issue like database is not cleanly shut down, database files (.mdf or .ndf) turned corrupt, insufficient memory or disk space. It also described methods to fix Recovery Pending State in SQL Server Database.

You can fix the database by setting it to emergency state, and initiating the repair process or de-attaching and reattaching the db. But, such solutions have their own downsides:

• It can only resolve minor database issues.
• It does not ensure complete recovery of the database.
• It involves data loss risk, particularly, running ‘REPAIR_ALLOW_DATA_LOSS’ command can delete some data.

You can overcome these limitations by using Stellar Repair for MS SQL software. The software helps SQL users and database administrators (DBAs) to fix severe database corruption errors. Also, it ensures complete recovery of db components, while preserving data integrity.

About The Author

Charanjeet is a Technical Content Writer at Stellar®who specializes in writing about databases, e-mail recovery, and e-mail migration solutions. She loves researching and developing content that helps database administrators, organizations and novices to fix multiple problems related to MS SQL and MySQL databases and Microsoft Exchange.