Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database IN RECOVERY Expand / Collapse
Author
Message
Posted Tuesday, May 20, 2014 3:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:33 AM
Points: 16, Visits: 282
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!
Post #1572533
Posted Tuesday, May 20, 2014 3:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 43,008, Visits: 36,166
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 2008, MVP
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

Post #1572538
Posted Tuesday, May 20, 2014 6:26 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, July 21, 2014 2:56 AM
Points: 2,603, Visits: 2,061
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."
Post #1572614
Posted Tuesday, May 20, 2014 6:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:33 AM
Points: 16, Visits: 282
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!
Post #1572634
Posted Tuesday, May 20, 2014 7:02 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 43,008, Visits: 36,166
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 2008, MVP
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

Post #1572635
Posted Tuesday, May 20, 2014 7:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:33 AM
Points: 16, Visits: 282
Hi
error 1450:
Insufficient system resources exist to complete the requested service.



Where there is a will... There is a way!
Post #1572649
Posted Wednesday, May 21, 2014 8:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:33 AM
Points: 16, Visits: 282
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!
Post #1573195
Posted Thursday, May 22, 2014 1:59 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:33 AM
Points: 16, Visits: 282
So Database has not come back online. In recovery still... Any suggestions please? Thanks.

Where there is a will... There is a way!
Post #1573451
Posted Thursday, May 22, 2014 2:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 1:12 AM
Points: 43,008, Visits: 36,166
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 2008, MVP
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

Post #1573454
Posted Thursday, May 22, 2014 9:10 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 30, 2014 7:33 AM
Points: 16, Visits: 282
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. :)

You are a star Gail :)


Where there is a will... There is a way!
Post #1573620
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse