Database goes into "In Recovery" due to drive full

Question

Database goes into "In Recovery" due to drive full

Viewing 6 posts - 16 through 20 (of 20 total)

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

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

AppSup_dba (9/4/2014)
Error log:
4078 transactions rolled forward in database 'database' (9). This is an informational message only. No user action is required.
Recovery of database 'database' (9) is 4% complete (approximately 146199 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
1 transactions rolled back in database 'database' (9). This is an informational message only. No user action is required.
Recovery is writing a checkpoint in database 'database' (9). This is an informational message only. No user action is required.
Recovery completed for database database (database ID 9) in 128 mins (s) (analysis xx ms, redo xx ms, undo xx ms.) This is an informational message only. No user action is required.

Please post the last few messages before that.

Are you sure the DB isn't going offline before going into recovery? Yes database goes offline and restarted due to rollback process failed (I stand corrected on this)

The rollback is failing? Where do you see that?

Do you think long recovery time is normal (as index rebuild is also long IO intensive operation) or we can reduce this somehow?

Potentially, yes. The index rebuild has to be rolled back.

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

AppSup_dba SSCrazy Points: 2215 More actions · Answer 2

I concluded Rollback failure from these log entries.

Error log

During undoing of a logged operation in database '', an error occurred at log record ID (xx)

Database was shutdown due to error 9002 in routine 'XdesRMReadWrite::RollbackToLsn'.

Cheers

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

AppSup_dba (9/4/2014)
I concluded Rollback failure from these log entries.
Error log
During undoing of a logged operation in database '', an error occurred at log record ID (xx)
Database was shutdown due to error 9002 in routine 'XdesRMReadWrite::RollbackToLsn'.

And that's why I was asking for error log entries....

So yes, the DB was restarted and hence entered the recovering state.

Would there perhaps be any more error log entries which you haven't yet shared?

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

AppSup_dba SSCrazy Points: 2215 More actions · Answer 4

That is it... nothing more than some login failures...

Cheers

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

If the problem reoccurs, call Microsoft customer support and open a case. A rollback shouldn't fail and take the DB offline, for any reason.

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

AppSup_dba SSCrazy Points: 2215 More actions · Answer 6

AppSup_dba

SSCrazy

Points: 2215

September 5, 2014 at 7:28 am

#1743516

point noted mam.. Thanks for your help !

Cheers