Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a database that has entered "Recovery Mode". It was working fine, but ended up in this status a few days ago.

I have been told that a cause can be the partition holding the datafiles running out of space. However in this case the drive has plenty of space. The log files for the database are also of a reasonable size (300-450mb)

I found this script on google that is meant to return the about of time a database has left before recovery is complete. It dosn't give me any results. We do not have a backup of the database, and we really need its data back

DECLARE @DBName VARCHAR(64) = 'databasename'

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT]  VARCHAR(MAX))

INSERT INTO @ErrorLog
EXEC sys.xp_readerrorlog 0, 1, 'Recovery of database', @DBName

SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT])  - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]

FROM @ErrorLog ORDER BY [LogDate] DESC
share|improve this question
1  
Has it been restored at all (from backup)? Is autoclose on? – gbn Jan 31 at 15:30
supporter organization of database says that there is no any backup. i dont know what happened because i didn't see this database before this problem – Pezhman Parsaee Jan 31 at 15:41
i can't see table and other content of database and i can not run query against databse. this error was happened when i try to run query against this database : Database 'Manager' is being recovered. Waiting until recovery is finished. – Pezhman Parsaee Jan 31 at 15:45
1  
Impossible to answer without relevant parts of errorlog. Post more info to get sensible answers. Don't take anyone's advice for good, especially if suggests deleting or moving files. – spaghettidba Jan 31 at 15:50

2 Answers

up vote 1 down vote

Check out Paul Randal's recommendations: Set the database to EMERGENCY mode, the database will be accessible to sysadmins only. It'll be read-only, so nothing can be written to the transaction log

Using EMERGENCY mode to access a RECOVERY PENDING or SUSPECT database

share|improve this answer
up vote 0 down vote

I often see databases enter this mode when they are the target of a log-shipping (or automated apply) and a single log file has been damaged.

Unless you have a backup of the database you trust, and transaction logs to take it back up to date the only thing I can suggest is this. BUT be warned, you may loose data integrity.

ALTER DATABASE [EmergencyDemo] SET SINGLE_USER;
GO
DBCC CHECKDB (N'DATABASENAME', REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS, NO_INFOMSGS;
GO

Please Note: You should really find out why your database ended up in recovery mode before trying to repair it. The SQL error logs should give you an idea. Also in the future, I can't stress how important proper backups are (ensure you keep them on a seperate system from the database server!).

share|improve this answer
1  
This is one way to remedy this, but I would strongly recommend establishing the reason why your database is in recovery mode before attempting any solution to bring it out of recovery. This information should be provided in the SQL Error Log. – Mike Fal Feb 1 at 20:56
I agree with Mike (answer updated), this routine should only be used as a last ditch resort to bring a db back from the dead. – jamesakadamingo Feb 1 at 21:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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