SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database IN RECOVERY


Database IN RECOVERY

Author
Message
M.DBA
M.DBA
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 336
Hi

Please help with this, We have a SQL Server 2005 cluster. Database has been In Recovery for more than 12 hours sicne server was failed over. . The errors we can see is 1450 errors. This started after DBCC CheckDB created a SQL Dump. Could you please let me know what is our best option, we have a backup form 4 days ago. Thank you very much in advance.

result of select * from sys.databases = Recovering

Thanks.

Where there is a will... There is a way!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218525 Visits: 46278
Wait.
Recovering means that SQL is running restart-recovery on the database. When it finishes the DB will come online.

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


free_mascot
free_mascot
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7325 Visits: 2250
Check if any disk SAN issue.

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."
M.DBA
M.DBA
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 336
Hi, Thanks for your reply, but the database has been in recovery for too long, usually takes less than a minut, we think it is very unlikely for it to come online. Operating system error 1450 keeps coming up. Disks have been checked, all the databases have come online except this one. the size of db is around 500GB, and SQL Server memory is 32 Gb. Do you think if it is the size of database? lack of memory? ...Thanks

Where there is a will... There is a way!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218525 Visits: 46278
If the database state is RECOVERING, then it is running the recovery process and will come online at the end of that. Probably there's a large transaction that needs to be rolled back, or lots of committed transactions whose changes hadn't been written to the data file.

For those of us who don't memorise operating system errors, what is error 1450?

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


M.DBA
M.DBA
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 336
Hi
error 1450:
Insufficient system resources exist to complete the requested service.

Where there is a will... There is a way!
M.DBA
M.DBA
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 336
Hi

Database has been in recovery for more than 24 hours and we are 99.99% sure it will never come out of recovery. It keeps throwing 1450 errors every two minutes. Is there any way to say where these errors come from? Thank you in advance.

Where there is a will... There is a way!
M.DBA
M.DBA
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 336
So Database has not come back online. In recovery still... Any suggestions please? Thanks.

Where there is a will... There is a way!
GilaMonster
GilaMonster
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218525 Visits: 46278
Wait, or drop it and restore from your last backups.

Or maybe call MS support and get someone on site if your agreements with them allow. Will cost. They might be able to track down whatever is causing the low resource warning. I assume you've already checked memory, IO, all the usual suspects.

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


M.DBA
M.DBA
SSC Journeyman
SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)SSC Journeyman (98 reputation)

Group: General Forum Members
Points: 98 Visits: 336
Hi

We rebuilt fulltext index on another database and we saw database starting to recover and after 15 minutes it finished, the last error before the recovery started was transaction spid 20 was deadlocked on lock resources with another process and has been chosen as victim.

Very good news for us! Now the 1450 errors are still coming up, it looks like we need to apply SP4, looks like MDF can not expand, although there is 90 GB free space on the disk. Apparently it is a bug! I will provide more info later. but thank you for reading and replying to my questions. Smile

You are a star Gail Smile

Where there is a will... There is a way!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search