My Database is (In Recovery) - SQL2005 over Windows 2003

Question

Post reply

My Database is (In Recovery) - SQL2005 over Windows 2003

Willian Funes

SSC Enthusiast

Points: 123
More actions
December 28, 2009 at 5:20 pm

#137762

I have a critical issue with two databases that today appear like "Database (In Recovery)"
I have tried to find the reasons but I haven't found nothing
related to that problem (No Jobs, No Backups,..), the thing
is users can't access because is unavailable, even I try to give simple 'sp_helpdb' from query analyzer and the answer never
appears, just say "Executing" becasue the server is trying to
showme all database and I think this two databases block the
instruction.
¿Is the DB corrupted and I need to go for a backup and
shuting down the services?
I will appreciate any coments to solve this problem,

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

josantia Ten Centuries Points: 1255 More actions · Answer 1

Hi,

You can check the status with:

select * from sys.databases

also, you can check in the ERRORLOG (SQL) or Event Viewer if something happened with those databases.

Regards.

Jose S. Oyervides.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

Check the SQL Server logs to find out if the instance has been restarted. It might be a situation where the system crashed and it is taking a long time to recover the database after restarting.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ultimate sql Mr or Mrs. 500 Points: 575 More actions · Answer 3

This normally happens when SQL instance is restarted and SQL Server tries to recover the database.

Like other members, I would also recommend checking the server logs to find out exactly what has happened. Without finding the real cause, finding a solution would be difficult.

Also specify your database configuration? Is it on a standalone server? Do any of these databases take part in log shipping?

[font="Tahoma"]Fahim Ahmed[/font]
[font="Times New Roman"]Knowledge is a journey, not a destiny [/font]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Query sys.databases for the state.

Open the SQL error log and look for messages relating to that DB. If you see things like

"Recovering database <db name> (phase 2 of 3) Estimated time remaining 2356 seconds" then just be patient and wait. It means that the service has restarted or some other operation has forced restart-recovery on a database.

If you don't see messages like that, but do see other messages, post them here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Willian Funes SSC Enthusiast Points: 123 More actions · Answer 5

Hi, the instruction "select * from sys.databases" work fine and I get all DB but when I try to access the DB or expanding objects it show me a message like "The dababase is not accessible" and the DB change to (In Recovery)

As additional information, I had a abrupted server down last Tuesday and I think this would be the reason, BUT ¿How long time I have to wait for recovering complete?

So, the event viewer just show me an alert related to the Report Server but I don't think this is reason.

Do you recomend shutdown the server a delete DB files for get a restore?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Willian Funes (12/29/2009)
Hi, the instruction "select * from sys.databases" work fine and I get all DB

Yes, it will work. sys.databases is in master. The reason for asking you to query it is to get the actual state of the DB. Look at the state_desc column for the DB in question.

As additional information, I had a abrupted server down last Tuesday and I think this would be the reason, BUT ¿How long time I have to wait for recovering complete?

What does the error log say? If it's saying something like 'estimated time remaining x seconds' then you wait

Do you recomend shutdown the server a delete DB files for get a restore?

No. I recommend looking in the error log, looking for messages relating to the DB and figuring out whether it will recover or not. If you're unsure, post the error messages here.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Willian Funes SSC Enthusiast Points: 123 More actions · Answer 7

the log file show me a message "SQL server has encountered 1 occurrence(s) of cachestore flush for the bounds trees cachestore (part of plan cache) due to some database maintenance or reconfigure operation..

The instruction "select * from sys.databases" show this database in "Is Autoclose on = 1" and "Is Cleanly Shutdown = 1"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Willian Funes (12/29/2009)
the log file show me a message "SQL server has encountered 1 occurrence(s) of cachestore flush for the bounds trees cachestore (part of plan cache) due to some database maintenance or reconfigure operation..

There must be other messages in there. Ones relating to the DB that is 'in recovery'

The instruction "select * from sys.databases" show this database in "Is Autoclose on = 1" and "Is Cleanly Shutdown = 1"

Not interested in those columns. I need to know what the state_desc column shows for the DB in question.

That said, why is auto_close enabled?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Willian Funes SSC Enthusiast Points: 123 More actions · Answer 9

Willian Funes

SSC Enthusiast

Points: 123

December 29, 2009 at 8:16 am

#1097045

The state=2 AND state_desc is "RECOVERING"

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

So that means that restart-recovery is in process. Give it time and the DB will either come online or it will go suspect.

Error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Willian Funes SSC Enthusiast Points: 123 More actions · Answer 11

Thanks for all comments,

The database is online again, just changing the auto_close parameter to Off.

ALTER DATABASE [DATABASENAME] SET AUTO_CLOSE OFF

Reference: http://technet.microsoft.com/en-us/library/bb402929.aspx

Database Option to OFF:

This rule checks whether the AUTO_ CLOSE option is set OFF for SQL Server Standard and Enterprise editions. When AUTO_CLOSE is set ON, this option can cause performance degradation on frequently accessed databases because of the increased overhead of opening and closing the database after each connection. AUTO_CLOSE also flushes the procedure cache after each connection.

Best Practices Recommendations

If a database is accessed frequently (production), set the AUTO_CLOSE option to OFF for the database.

Another thing I found is that when database is set to autoclose = on, an operation that initiates an automatic database shutdown clears the plan cache for the instance of SQL Server.

http://blogs.msdn.com/sqlprogrammability/archive/2007/01/17/10-0-plan-cache-flush.aspx