I have a SAP instance running with a 'SQL Server' database that just recently got into recovery mode without a clear reason (no backup or restore was executed).

We noticed that the storage for logs (max 50gb) was 99% used. After cleaning unused data and turning the db to online, everything was back to normal. But I need to identify the root cause. What can you suggest, please, as a troubleshoot analysis approach?

More details:

MSSQL release 12.00.6329.01 DB Client Library msodbcsql13 Database Releases MSSQL 9.00.2047 or higher

It's connected to SAP system via Database Shared Library:

DBSL Version 753.02 DBSL Patch Level 510 SQL Server 2014 120

  • Databases go into recovery mode when something bad has happened. An abnormal restart for example will put the database into recovery mode, until it has a chance to process the leftover transactions recorded in the transaction log. Backups never put the database into recovery mode. It's quite likely that lack of space didn't let the database complete the recovery operation, so it stopped waiting for the DBA to clean up space – Panagiotis Kanavos Feb 3 at 8:28
  • 4
    Check ERRORLOG for related messages – Denis Rubashkin Feb 3 at 9:11
  • I have also run into a similar situation when the log file filled the drive. SQL reserve some space in the log file to rollback transaction but it seems that in certain scenario, the rollback is not able to complete du to a "no more space" condition. It then goes into "recovery". – Dominique Boucher Feb 4 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.