Database in recovery due to insuffcient space

Question

Post reply

Database in recovery due to insuffcient space

VastSQL

SSC-Dedicated

Points: 36097
More actions
September 11, 2014 at 3:35 am

#290712

Hi Experts,
One of our DB is in recovery state due to insufficient space in drive where the log files resides. Is there anyway we can come out of this situation?
TIA

Viewing 13 posts - 1 through 12 (of 12 total)

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

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

Database state is RECOVERING? (check sys.databases)

Check error logs. Insufficient space should not do that to a DB.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

Thanks Gail,

In SQL error log it is showing recovering and it took 19 minutes to move from 89% to 90%.

All log files are in D drive and have only 8MB free space available.

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

Clear up some space on D (move other DB's logs or allocate more space) and wait.

What error messages are there in the error log? A full log should not send a DB into recovery.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

sys.databases returned 'RECOVERING'.

No other files exists in that drive other than the two log files of this database. The 2 files take 599GB space.

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 5

GilaMonster (9/11/2014)
Clear up some space on D (move other DB's logs or allocate more space) and wait.
What error messages are there in the error log? A full log should not send a DB into recovery.

Thanks Gail..

Can you please let know what happens to a DB if there is no space for data file or log file to grow??(Suspect??)

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

No. Should just go read only. Though since running out of space in this case, for some reason, sent the DB into recovery, there's a chance that will happen again.

You need to make some space on that drive somehow. If the log already ran out of space, then unless this was some unusual operation or a maintenance failure, it means the log needs more space.

Asking again, what error messages are there in the error log? A full log should not send a DB into recovery.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 7

Thanks a lot Gail..You are awesome..

Below error are logged in error log before starting recovery.

Database ABC has more than 1000 virtual log files which is excessive. Too many virtual log files can cause long startup and backup times. Consider shrinking the log and using a different growth increment to reduce the number of virtual log files.

During undoing of a logged operation in database 'ABC', an error occurred at log record ID (7718257:21496:62). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

D:\Data\ABC_20140814_1545_Log3.ldf: Operating system error 112(There is not enough space on the disk.) encountered.

This is a VLDB having size around 4TB,now its 96% complete.

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

I'd open a case with CSS about this:

During undoing of a logged operation in database 'ABC', an error occurred at log record ID (7718257:21496:62). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

You still need to make sure you have enough space on that disk.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 9

Thanks Gail,

After almost 16 hours DB is up.

Able to shrink the log files and Now the drive is having 614GB free space.

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

Have you fixed what caused the log to grow? If not, this is just going to happen again.

Shrinking the log isn't going to stop it growing again if it needs that 600 GB.

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

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 11

Gail ,

Can you please guide me on how to find the root cause of log growth?

enriquemallon Ten Centuries Points: 1239 More actions · Answer 12

Best thing to do is to collect what caused the log file to grow by setting up an alert