2

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
4
  • 1
    Has it been restored at all (from backup)? Is autoclose on?
    – gbn
    Commented Jan 31, 2013 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 Commented Jan 31, 2013 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. Commented Jan 31, 2013 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. Commented Jan 31, 2013 at 15:50

2 Answers 2

2

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

1

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!).

2
  • 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
    Commented Feb 1, 2013 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. Commented Feb 1, 2013 at 21:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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