1 / 4

Solve recovery pending state of SQL sever Database

Here we will describe, how to fix recovery pending state in SQL Server Database.<br>

ashishsrivastava949
Download Presentation

Solve recovery pending state of SQL sever Database

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Methods to solve recovery pending state in SQL Server Database • Users who are working with the SQL server database might have to face the problems with recovery pending state once because of the repetitiveoccurrence. • So, to solve this kind of error this article will help to get rid off this state this article will help you to explains how to fix recovery pending state in SQL Server Database. • The database is a collection of data in a systematic way. Databases support manipulation and storage of data. Databases make easier management ofdata. • StructuredQuerylanguage(SQL)pronouncedas"S-Q-L"orsometimesas"See- Quel" is basically the standard language for dealing with RelationalDatabases. • First, we have to understand all about the SQL Databasestates. • SQL DatabaseStates • If one or more of its key files are in the inconsistent state, an SQL databaseis • considered to be damaged. The database is marked with different states depending on how severe the damage is. Check the following fewstates: • Online – The database will stay online and available if one of the data files has been damaged during a query or some otheroperation.

  2. Suspect–Ifthetransactionlogisdamagedandtherecoveryorrollback of a transaction is prevented from being completed, which could cause it tofail. • RecoveryPending–IftheSQLServerunderstandsthatitisnecessary to operate the recovery of the database, but something prevents it from starting. This is different from the state of SUSPECT because itcan • not be said that recovery will fail –it hasn't just begunyet. • After understanding the SQL Database States then move towards the reason behind the Recovery PendingStates: • Decoding where and what causes an error as important as finding a sure-shot alternative to it. A SQL database is marked in the state of Recovery Pending when recovery is required but can not be launched. This type of scenario mainly occurswhen: • The database is not shut down smoothly, i.e. at least one uncommitted transaction is active when the database is shut down and the log file is deleted. • Auserattemptedtotransferthelogfilestoafreshdrivetosolveserver performance problems, but in the process, the log files werecorrupted. • Database recovery can not be started due to a lack of memory or disk storage. • Causes of SQL Server Recovery PendingError? • When Memory space is either full or more limited in Databasepartition. • Because of Hardwarefailure. • Corrupt logfiles. • While shutting down, Unfinished tasks or some actions werepending. • When you start the server without any time gap then there is a probability of occurrence of thiserror. • There are two types of solution for this error: • 1. ManualMethods

  3. 2. AlternateMethod • Manual Methods • Method1.YouhavetoperformarepairbyrunningthefollowingSQLqueries: • ALTER DATABASE [D-BName] SETEMERGENCY; • GO • ALTER DATABASE [D-BName] set single_user GO • DBCCCHECKDB([D-BName],REPAIR_ALLOW_DATA_LOSS)WITH ALL_ERRORMSGS; • GO • ALTER DATABASE [DB-Name] set multi_user GO • Emergency mode marks the database as READ ONLY and disables logging and system administrators are grantedaccess. • These steps will solve the corruption of the file and create the online database systematically. • Method 2. Mark the emergency database, detach and re-attach the primary database • Execute the queries givenbelow: • ALTER DATABASE [DB-Name] SET EMERGENCY; • ALTER DATABASE [DB-Name] set multi_user EXEC sp_detach_db‘[DB-Name]’

  4. EXEC sp_attach_single_file_db @DBName = ‘[DB-Name]’, @physname = N'[mdfpath]’ 2. These commands will result in the server getting rid of the corrupt log and automatically building a fresh one. You can also read, How to retrieve Archived emails inOutlook. Notice: You should guarantee that you have adequate backups of the database in question before starting any of these repair processes. If anything goes wrong, this is to have a failed-safe copy. Also, remember that these are extremely technical processesandifyouareuncertainordonothaveadequatetechnicalknowledge,you should not be performingthem. After discussing all the manual methods hope you will find the solution to the problem related to SQLDatabase. Conclusion Weaddressedthemanualtechniquesofthe"SQLserverrecoverypending"request that was most anticipated and there may be several factors that may cause an offline SQLdatabasetogo.AnyDBASQLuser'spriorityistosolvefrequentSQLmistakes and get the database back on track as quickly aspossible. AUTHOR:- Ashish Srivastava is a content marketer, Blogger and maintaining Social Media Optimization in CigatiSolutions. A writer by day and reader by night, He worked with various other brands and create value for them. Find him on LinkedIn here: @ashishSrivastava

More Related