0

I have a database which in stuck "in recovery" mode. The .ldf file is very large and the free disk space is very low. I suspect that the large size of the .ldf file is causing the database to be stuck "in recovery" mode.

The question: Is there a safe way of removing the .ldf file without losing database data?

Vérace
  • 30,923
  • 9
  • 73
  • 85
Tornike Shavishvili
  • 103
  • 1
  • 6

3 Answers3

3

You want to consult the errorlog file to see the status of the recovery process.

"In recovery" means that is is starting up the database (you might have re-started your SQL Server or attached a database, for instance) and it need to get it into a clean state. If this state persists longer that a minute, then it is likely performing a huge rollback. Just wait it out. You can't delete the ldf as long as SQL server is running. If you stop SQL Server and delete the ldf, then you will get into below state:

"Recovery pending". This means that recovery failed and it is time to perform a restore. I have a feeling that you prefer to wait out the recovery process (above paragraph).

A risk is that the ldf file need to grow during recovery and you this grow fails. So investigate if you can free up disk space by some other means to stop that from happening.

Tibor Karaszi
  • 18,269
  • 2
  • 17
  • 30
2

If your log has uncommitted transactions, you either need to wait for recovery to complete (which, yeah, can take a long time, depending on the transactions that have to be recovered), or you lose that data. There is no other way to get that data that's in the transaction log except through the recovery process.

The good news is, your MDF file(s) is fine. If you want to completely toss the data that's in the log (stuff in flight, inserts, deletes, updates), then you can follow one of the paths outlined in this article to rebuild your log file. That will get your database back online, but without the data from the log.

Grant Fritchey
  • 4,615
  • 20
  • 19
0

In addition to what Tibor and Grant have written, I would also check what is your database recovery model by right-clicking Database->Properties->Options recovery mode and if it is FULL, how / if are you backing up transaction log regularly. Because if you have recovery model: FULL and are not backing up your transaction log, it grows, grows and grows ... UNTIL you carry out at least 1 full backup and 1 transaction log backup (long story short: backing up T-LOG enables the space re-usage so the log does not grow forever).

Sranda
  • 360
  • 1
  • 11