recovery pending sql что значит
Ручной Способ исправить Состояние ожидания восстановления SQL Server
Люди, которые использовали SQL-сервер, возможно, однажды столкнулись с ошибкой SQL база данных в состояние ожидания восстановления из-за ее повторного появления. Если вы не знаете, почему происходит эта ошибка, значит, ваш раздел базы данных, вероятно, заполнен. SQL не может открыть базу данных и не может заблокировать файл базы данных, который очень похож на базу данных в автономном режиме. Это больше похоже на то, что что-то мешает запуску сервера. В этом блоге мы собираемся обсудить, как исправить состояние ожидания восстановления SQL Server с помощью наилучших возможных методов. Прежде чем продолжить, давайте узнаем, каковы причины этой ошибки.
Причины – Состояние Ожидания восстановления базы данных SQL Server
Мгновенное Решение: Используйте средство восстановления 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.
Вывод
Recovery pending sql что значит
� ����� ���������
2014-09-01 11:39:03.00 spid81 Starting up database ‘db’.
2014-09-01 11:39:03.01 spid81 Error: 824, Severity: 24, State: 6.
2014-09-01 11:39:03.01 spid81 SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x00000aaa). It occurred during a read of page (1:0) in database ID 51 at offset 0000000000000000 in file ‘D:\..\db_Data.MDF’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2014-09-01 11:39:04.53 spid81 Error: 824, Severity: 24, State: 2.
2014-09-01 11:39:04.53 spid81 SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0xaaaaaaaa; actual signature: 0x00c30412). It occurred during a read of page (2:0) in database ID 51 at offset 0000000000000000 in file ‘D:\..\db_Log.LDF’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2014-09-01 11:39:04.53 spid81 Error: 5105, Severity: 16, State: 1.
2014-09-01 11:39:04.53 spid81 A file activation error occurred. The physical file name ‘D:\..\db_Log.LDF’ may be incorrect. Diagnose and correct additional errors, and retry the operation.
��� ������ ������ ���� ��� ���� �� ��������, ��� � �������� RECOVERY_PENDING � ���� ���� ������� �� ����������
dbcc checkdb ���� �� �����������, �����
Database ‘db’ is being recovered. Waiting until recovery is finished. |
���� �����-�� ������� ������� � emergency?
����� ��� ������� ������ (���� �� ���, ��������� ������, ������ ������� ���� ������ ������ ����)
| |
Glory Member ������: | http://support.microsoft.com/?kbid=2015756 |
1 ��� 14, 14:52����[16519972] �������� | ���������� �������� ���������� |
| |
PassedBI Member | |
1 ��� 14, 16:31����[16520410] �������� | ���������� �������� ���������� |
| |||
o-o Guest |
The boot page is inaccessible so in effect the database is inaccessible too, this is what the database state means in this case. So what does this mean? If the boot page is corrupt, you can’t run DBCC CHECKDB so you can’t possibly run repair, and you can’t put the database into EMERGENCY mode so you can’t extract data into a new database. It means that there’s NO WAY to recover from a corrupt boot page EXCEPT to restore from backups. One more reason to have backups� ��� ��� ������� ��� �� �� ����������� ����: Easy Ways to Fix Recovery Pending State in SQL Server 2014/2016/2017In everyday use, the SQL server database may face many technical issues and are tough to settle. One of the problems is «Recovering pending state in SQL server database». If you encounter this issue unexpectedly, don’t worry. Resort to the fixes here to solve recovery pending state in SQL server database. Besides, if you are not a professional in SQL server database issues and do not have the confidence to settle this problem, you can use EaseUS MS SQL Recovery to help you do it in a simple and effective way. Three States of SQL Server DatabasesIf a single or multiple core MDF/NDF files corrupted, an SQL database is likely to be damaged. Different levels of damage will show up in different states. Let’s look into details. You can execute the following query statement to know the SQL server state. SELECT name, state_desc from sys.databases Reasons forВ Recovery Pending State in SQL ServerSeveral factors can lead to recovery pending state in SQL Server database. The main reasons are listed as follows. HowВ to Fix Recovery Pending State in SQL ServerВ DatabaseYou can fix recovery pending state issue in SQL Server database manually with some queries. Or to avoid worse problem due to misoperations, you can use a professional MS SQL recovery tool to make you through. 2 Manual Solutions to Start SQL Database RecoveryMethodВ 1: Mark SQL Database in Emergency Mode and start a forceful recoveryThe emergency mode will mark the SQL database as READ_ONLY, disableВ logging. At this time, DBA is only allowed to access. Entering emergency mode and start database recovery can solve any technical issue. And the database will automatically come out of the EMERGENCY mode. ALTER DATABASE [DBName] SET EMERGENCY; ALTER DATABASE [DBName] set single_user DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS; ALTER DATABASE [DBName] set multi_user Solution 2: Mark SQL Database in Emergency Mode, disconnect the main database and re-connect it againThese queries will cause the server to get rid of the corrupt log and build a new one automatically.В ALTER DATABASE [DBName] SET EMERGENCY; ALTER DATABASE [DBName] set multi_user EXEC sp_detach_db вЂ[DBName]’ EXEC sp_attach_single_file_db @DBName = вЂ[DBName]’, @physname = N'[mdf path]’ An Easier and Risk-Free Way to Fix Recovery Pending State in SQL ServerThe above manual solutions can fix recovery pending state issue effectively. If you are a pro at SQL Server database issues, they are your first choices. However, if you don’t know much about SQL Server database, we highly recommend you try a safer and automated workaround. Use MS SQL recovery software to repair the corrupted database in 823/824/825 errors. Step 1.В Download and run EaseUS MS SQL Recovery. Step 2.В Click the two dots (Browse button) to choose the target MDF file or click «Search» and select the desired file to repair from the list found below. Then, click «Repair» to start the repairing process.В Step 3. When the process completes, a Window will appear to confirm the successful analysis. All the deleted tables & records recovered items will be listed on the left pane in the name of the original tables. Step 4. Click the «Export» button in the bottom right corner of the screen. You can save the recovered database to database & SQL scripts based on your need. Then, you need to enter Server/Instance Name and connect to the server. If you choose to «Export to database», you can create a new database or export to an existing database.В Troubleshoot AlwaysOn availability databases in Recovery Pending or Suspect state in SQL ServerThis article describes the errors and limitations of an availability database in Microsoft SQL Server that is in a Recovery Pending or Suspect state and how to restore the database to full functionality in an availability group. Original product version: В SQL Server 2012 SummaryAssume that an availability database that is defined in an AlwaysOn availability group transitions to a Recovery Pending or Suspect state in SQL Server. If this occurs on the availability group’s primary replica, database availability is affected. In this situation, you can not access the database through the client applications. Additionally, you can not drop or remove the database from the availability group. For example, assume SQL Server is running and an availability database is set to the Recovery Pending or Suspect state. When you query the dynamic management views (DMVs) at the primary replica by using the following SQL script, the database might be reported in a NOT_HEALTHY and RECOVERY_PENDING state or in a SUSPECT state as follows: Additionally, this database may be reported as being in the Not Synchronizing / Recovery Pending or Suspect state in SQL Server Management Studio. When the database is defined in an availability group, the database can not be dropped or restored. Therefore, you have to take specific steps to recover the database and return it to production use. More informationThe following content discusses the errors and limitations of an availability database that is in a Recovery Pending state in various situations. Database status prevents restoring database You try to run the following SQL script in order to restore the database that has the RECOVERY parameter: When you run this script, you receive the following error message because the database is defined in an availability group:
Msg 3013, Level 16, State 1, Line 1 Database status prevents dropping database You try to run the following SQL script in order to drop the database: When you run this script, you receive the following error message because the database is defined in an availability group:
Database status prevents removing database from availability group You try to run the following SQL script to remove the database from the availability group: When you try to run this script, you receive the following error message because the availability database belongs to the primary replica:
Because of this error message, you may be compelled to fail over the database. After the database is failed over, the replica that owns the recovery pending database is in the secondary role. In this situation, you try to execute the following SQL script again in order to remove the database from the availability group at the secondary replica: However, you still can not remove the database from the availability group, and you receive the following error message because the database is still in Recovery Pending state:
Resolution when the database is in the secondary roleTo resolve this issue, take the following general actions: To take these actions, connect to the new primary replica, and then run the ALTER AVAILABILITY GROUP SQL script to remove the replica that is hosting the failed availability database. To do this, follow these steps. These steps assume that the primary replica first hosts the damaged database. Therefore, a failover must first occur to transition the replica that is hosting the damaged database into a secondary role. Connect to the server that is running SQL Server and that is hosting the secondary replica. Run the following SQL script: Run the following SQL script to remove the replica that is hosting the damaged database from the availability group: Resolve any issues on the server that is running SQL Server and that might contribute to the database failure. Add the replica back into the availability group. Resolution when the primary replica is the only replica in the availability groupIf the primary replica hosts the damaged database and is the only working replica in the availability group, the availability group must be dropped. After the availability group is dropped, your database can be recovered from a backup, or other emergency recovery efforts can be applied to restore the databases and to resume production. To drop the availability group, use the following SQL script : At this point, you can try to recover the problematic database. Or you can restore the database from the last-known good backup copy. Resolution when you drop the availability groupWhen you drop an availability group, the listener resource is also dropped and interrupts application connectivity to the availability databases. To minimize application downtime, use one of the following methods to sustain application connectivity through the listener and drop the availability group: Method 1: Associate the listener with a new availability group (role) in Failover Cluster ManagerThis method lets you maintain the listener while dropping and re-creating the availability group On the instance of SQL Server to which the existing availability group listener is directing connections, create a new, empty availability group. To simplify this process, use the Transact-SQL command to create an availability group that has no secondary replica or database: Start Failover Cluster Manager, and then click Roles in the left pane. In the pane that lists the roles, select the original availability group. In the bottom-middle pane under the Resources tab, right-click the availability group resource, and then click Properties. Click the Dependencies tab, delete the dependency to the listener, and then click OK. Under the resources, right-click the listener, click More Actions, and then click Assign to Another Role. In the Assign Source to Role dialog box, select the new availability group, and then click OK. In the Roles pane, select the new availability group. In the bottom-middle pane, under the Resources tab, you should now see the new availability group and the listener resource. Right-click the new availability group resource, and then click Properties. Click the Dependencies tab, select the listener resource from the drop-down box, and then click OK. In SQL Server Management Studio, use Object Explorer to connect to the instance of SQL Server that hosts the primary replica of the new availability group. Click AlwaysOn High Availability, click the new availability group, and then click Availability Group Listeners. You should find the listener. Right-click the listener, click Properties, type the appropriate port number for the listener, and then click OK. This makes sure that applications that use the listener can still use it to connect to the instance of SQL Server that is hosting the production databases without interruption. The original availability group can now be completely removed and re-created. Or the databases and replicas can be added to the new availability group. If you re-create the original availability group, you should reassign the listener back to the availability group role, set up the dependency between the new availability group resource and the listener, and then reassign the port to the listener. To do this, follow these steps: Method 2: Associate the listener with an existing SQL Server Failover Clustered Instance (SQLFCI)If you are hosting your availability group on a SQL Server Failover Clustered Instance (SQLFCI), you can associate the listener clustered resource with the SQLFCI clustered resource group while you drop and then re-create the availability group. Start Failover Cluster Manager, and then click Roles in the left pane. In the pane that lists the roles, select the original availability group. In the bottom middle pane under the Resources tab, right-click the availability group resource, and then click Properties. Click the Dependencies tab, delete the dependency to the listener, and then click OK. In the bottom middle pane under the Resources tab, right-click the listener, click More Actions, and then click Assign to Another Role. In the Assign Resource to Role dialog box, click the SQL Server FCI instance, and then click OK. In the Roles pane, select the SQLFCI group. In the bottom middle pane, under the Resources tab, you should now see the new listener resource. This makes sure that applications that use the listener can still use it to connect to the instance of SQL Server that hosts the production databases without interruption. The original availability group can now be removed and re-created. Or the databases and replicas can be added to the new availability group. After the availability group is re-created, reassign the listener back to the availability group role. Then set up the dependency between the new availability group resource and the listener, and reassign the port to the listener: Method 3: Drop the availability group, and then re-create the availability group and listener with the same listener nameThis method will result in a small outage for applications that are currently connected because the availability group and listener are dropped and then re-created: Drop the availability group. This will also drop the listener. Immediately create a new, empty availability group that includes the listener definition, on the same server that hosts the production databases. For example, assume that your availability group listener is aglisten. The following Transact-SQL statement creates an availability group with no primary or secondary database, but it also creates a listener named aglisten. Applications can use this listener to connect. Recover the damaged database. Then add it and the secondary replica back to the availability group.
|