September 16, 2010 at 1:17 pm
sqlbuddy123 (9/16/2010)
1. Free up some space on D drive and see that if the DB recovers. This may include shrinking other databases to free up some space.
The database will not recover by itself, even if more space is made, because it is in the RECOVERY_PENDING state
2. Wait for the db to recover. But it has been already 2 weeks that it's in the recovering state.
It's not in the RECOVERING state, it's RECOVERY_PENDING and you can wait until kingdom come, it will remain in that state unless something is done
3. Restart SQL Server.
If you restart SQL without making space, the DB will go straight back into the RECOVERY_PENDING state and stay there.
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
September 16, 2010 at 1:35 pm
So.......you're saying that freeing up space on the drive will not do anything and that bouncing the server will not do anything? Which means that my only recourse is to restore the database?
September 16, 2010 at 1:43 pm
First of All , by recovering state I mean RECOVERY_PENDING
The database will not recover by itself, even if more space is made, because it is in the RECOVERY_PENDING state
A cause has not been found for the DB entering into RECOVERY_PENDING state. So my thought was to check if this help for some reason.
It's not in the RECOVERING state, it's RECOVERY_PENDING and you can wait until kingdom come, it will remain in that state unless something is done
Just a rephrase of what I said in 2.
If you restart SQL without making space, the DB will go straight back into the RECOVERY_PENDING state and stay there.
Agree with this. I mentioned that in pt 1. I didn't mean just restarting SQL Server will fix the problem. Please refer to 1 & 2.
Thank You,
Best Regards,
SQLServer
September 16, 2010 at 1:48 pm
hallhome (9/16/2010)
So.......you're saying that freeing up space on the drive will not do anything and that bouncing the server will not do anything? Which means that my only recourse is to restore the database?
Not individually.
If you can free up some space and then restart SQL, it may (emphasis MAY) be possible for SQL to start and complete the database recovery, at which point we should be able to address the root cause.
Restoring from that backup is probably the easiest way to get this DB back, making space (by moving the other databases) and then restarting SQL may fix the problem, depending on whether you can make enough space for the recovery to complete or not.
If you do go the backup route, please, please, please check that the backup is restorable (by restoring it on a test server) first.
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
September 16, 2010 at 1:49 pm
If for some weird reason the error leading to the pending state is erased from the log, then restarting the servger might get it logged this time (not sure, but sounds reasonable).
That should tell us the next step to take from here.
September 16, 2010 at 1:59 pm
I think the cause is known - the database is using FULL recovery model
and the transaction logs have never been backed up. Only full database backups have been taken.
September 16, 2010 at 2:04 pm
hallhome (9/16/2010)
I think the cause is known - the database is using FULL recovery modeland the transaction logs have never been backed up. Only full database backups have been taken.
Thing is, that shouldn't have resulted in the DB getting put into the recovery_pending state. SQL should have simply marked it read-only as soon as there was no more space in the log. For it to be RECOVERY_PENDING, something must have happened to require a recovery that SQL couldn't start.
That's what's concerning me and why I was asking for the errors from the 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
September 16, 2010 at 2:09 pm
hallhome (9/16/2010)
I think the cause is known - the database is using FULL recovery modeland the transaction logs have never been backed up. Only full database backups have been taken.
Same difference. You either know the problem and solutions or you don't.
Gail is advising a very Wise way to get back online safely.
If you're so sure you're short on HD space and that it's your ONLY problem, shut down the server, add a drive, move the 5% of "extra" files there and be done with it.
But I'd still listen to Gail on this one...
September 16, 2010 at 2:10 pm
@Gail - just curious... how did you acquire this mountain of knowledge on disaster recovery of failed databases? Like I mentioned earlier, you're one of the best out there... and the only ones I know of that are better than you have insider knowledge.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 16, 2010 at 2:15 pm
Ninja's_RGR'us (9/16/2010)
But I'd still listen to Gail on this one...
If I had any database problem, I'd be following every single word that Gail says concerning it to the "T".
@hallhome - Yes, Gail IS this good. She's really trying to help you. Do every single thing she asks, and you just might get out of this. I know that there is an incredible amount of pressure on you... try to resist it, and follow her advice.
@Gail - out of curiosity, would adding a drive and configuring it as a "mount point"(?) that expands the D drive be an alternative for freeing up space?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 16, 2010 at 2:29 pm
WayneS (9/16/2010)
@Gail - just curious... how did you acquire this mountain of knowledge on disaster recovery of failed databases? Like I mentioned earlier, you're one of the best out there... and the only ones I know of that are better than you have insider knowledge.
Sat at the master's feet and learned.
Seriously - read everything that Paul R has ever written (everything). Principles, not the details. Details come from the principles, not the other way around. So if you understand (for eg) how the T-log is structured and used, you can conclude what will happen under various circumstances.
Oh, and read and attempt to help on every recovery-related question around (here, MSDN), and accept corrections without complaint (but ask for more details if necessary). Know when you're out of your depth and don't guess (in a disaster recovery situation, that's deadly)
Test out the situations and see how things work (fill a DB's log, see what happens, then restart SQL, what happens?)
Have a reasonably good memory (of where to look for details)
p.s. As for insider knowledge, does mail contact with the SQL dev team count?
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
September 16, 2010 at 2:31 pm
WayneS (9/16/2010)
Ninja's_RGR'us (9/16/2010)
But I'd still listen to Gail on this one...If I had any database problem, I'd be following every single word that Gail says concerning it to the "T".
@hallhome - Yes, Gail IS this good. She's really trying to help you. Do every single thing she asks, and you just might get out of this. I know that there is an incredible amount of pressure on you... try to resist it, and follow her advice.
@Gail - out of curiosity, would adding a drive and configuring it as a "mount point"(?) that expands the D drive be an alternative for freeing up space?
It's a good idea, but I'd assume that ALL databases on that drive are readonly at this point... or about to be running out of space (since the shrink option seems to have been dismissed). So since all dbs are about to fail, I'd just worry about getting that one DB back from the dead.
Also since it's almost 5PM here, I'd even consider taking a backup of another DB, testing the backup and if it's good drop the db from the server. That will difinitely get you some space back and hopefully enough.
But again, that's just me and I'd do what Gail says :w00t:.
Good night.
September 16, 2010 at 2:34 pm
GilaMonster (9/16/2010)
p.s. As for insider knowledge, does mail contact with the SQL dev team count?
Yes, it sure would! (just not as convenient as personally knowing it...)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 16, 2010 at 2:36 pm
WayneS (9/16/2010)
@Gail - out of curiosity, would adding a drive and configuring it as a "mount point"(?) that expands the D drive be an alternative for freeing up space?
Don't think so. From what I know of mount points, you mount a drive as a directory. It doesn't make the drive larger, just makes a directory redirect to a separate drive (symlink from Unix days). It won't let a single file spread across the drives.
There is a way to extend a drive, think it requires dynamic drives, can't recall what else it needs. Getting outa my area here.
Of course, if this is a SAN LUN, it should be possible for the storage admin to extend it trivially.
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
September 16, 2010 at 2:42 pm
Ninja's_RGR'us (9/16/2010)
It's a good idea, but I'd assume that ALL databases on that drive are readonly at this point... or about to be running out of space (since the shrink option seems to have been dismissed).
Good point...
hallhome, please for now switch any user databases on that server into simple recovery model if they're not already. If they're all as you say (full recovery, no log backups) the other DBs are going to be having the same problem now or soon.
Also since it's almost 5PM here...
11pm here, I'm going to bed shortly. Catch you in about 9 hours.
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
Viewing 15 posts - 31 through 45 (of 86 total)
You must be logged in to reply to this topic. Login to reply