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 3 down vote favorite

I'm a junior DBA and I have a very strange problem with one database.

Today, after a power failure, one database (with Recovery: full) show (In recovery) in SSMS ie: myDatabase (In recovery) (database status: recovery, Shutdown)

After finish, the "recovery process" the database shows the name myDatabase without "(In recovery)". I thought that the problem was solved, but it was not.

When I started the application that uses that database, the legend "(In recovery)" appears again next to the name of my database.

I waited until the "recovery process" finished and then I took the database offline and bring it back to online.

I restarted the server, restarted the computer and when my application was running the legend appears again. In the SQL Server logs the message "Starting up database 'myDatabase'" appears few times. It seems that the database is working because I can insert data, but the state is showing that something it happens.

PLEASE HELP !!!

thanks in advance

Edit: When I ran this query: SELECT databasepropertyex('nyDatabase', 'STATUS') it shows Recovering, online, suspect and the back to online and then Recovering and so on.

share|improve this question
2  
have you tried running dbcc checkdb while the database says it's online? – DForck42 Feb 18 '11 at 20:05

migrated from stackoverflow.com Jan 21 '12 at 20:19

2 Answers

up vote 1 down vote

Whenever you bring a database online, it goes through a recovery process. I'm a bit confused by your phrasing of the problem though. Are you seeing the database go into recovery any time other than when the database is being brought offline (either from your original power failure or by your having brought it offline and then online again)? If so, the database is going offline for some other reason. Best to check the SQL server logs to see what's going on.

share|improve this answer
The database goes to In recovery when the application is working. The server log is not showing nothing interesting. The only abnormal thing is that I have 30 entries with a legend "Starting up database 'myDatabase'" – Andres Feb 18 '11 at 19:25
I'd say it's time to check the Windows event log for something like a disk failure then. The database should not be going starting up and going into recovery during normal operations. – Ben Thul Feb 18 '11 at 19:55
I've installed a new instance of sql server and put the old database "myDatabase" version on it and the problem still happens. – Andres Feb 18 '11 at 21:30
I know that when the server starts put every database in recovery (set recovery point) until Ready. But in my case, the database goes to online up to start the application that use it. After that the database is "myDatabase (In recovery)" (database->properties-Status:Recovering, Shutdown). If I close the application, the database goes to Status: Normal. This is driving me crazy. – Andres Feb 19 '11 at 6:05
up vote 1 down vote

I am not sure if this would help solve the issue, but you can give it a shot. Run -

RESTORE DATABASE YourDatabase WITH RECOVERY

See if the above brings the database out of recovering mode. If it doesn't then the problem may be something else.

Can you try the below command and see if the database is corrupt?

DBCC CHECKDB (‘YourDBname’) WITH NO_INFOMSGS, ALL_ERRORMSGS

If it detects corruption, then you might want to repair the database using DBCC CHECKDB

share|improve this answer

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.