December 28, 2009 at 5:20 pm
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,
December 28, 2009 at 5:35 pm
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.
December 28, 2009 at 8:07 pm
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
December 28, 2009 at 11:25 pm
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?
December 29, 2009 at 12:05 am
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
December 29, 2009 at 7:57 am
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?
December 29, 2009 at 8:10 am
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
December 29, 2009 at 8:11 am
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"
December 29, 2009 at 8:13 am
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
December 29, 2009 at 8:16 am
The state=2 AND state_desc is "RECOVERING"
December 29, 2009 at 8:20 am
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
December 29, 2009 at 11:05 am
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply