The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

Question

I am getting following error while I am trying to delete 355447 records in single delete query.

The transaction log for database is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I tried foll. solution, But still delete statement throwing same error.

DBCC SHRINKFILE(DBname_Log, 2)
BACKUP LOG gis_sync WITH TRUNCATE_ONLY
DBCC SHRINKFILE(DBname_Log, 2)

Please help me to solve.... Thanks

Answer 1

As an aside, it is always a good practice (and possibly a solution for this type of issue) to delete a large number of rows by using batches:

WHILE EXISTS (SELECT 1 
              FROM   YourTable 
              WHERE  <yourCondition>) 
  DELETE TOP(10000) FROM YourTable 
  WHERE  <yourCondition>

Answer 2

I was also facing the same issue.The conclusion I got is that This exception comes when the disk in which the database file is there is full.To resolve this issue I just increase the size of the disk .

Answer 3

As Damien said, you should find out the reason why your log is growing. Check out this post for an explanation:Transaction Log Reuse Wait

Deleting that many records will require significant log-space in itself, so if you can't make more room for the log file, you might have to delete those rows in several smaller steps. If you are using "full" recovery, you will have to take a log backup after every step.

On a side note, BACKUP LOG ... WITH TRUNCATE_ONLY is in general a very bad idea. If you are in full recovery mode, than this will break you backup chain and prevent you from doing a point-in-time restore. If you don't need point-in-time recoverability, use the recovery setting "simple" instead. Otherwise take a real log backup and store it together with you other backup files.

DBCC SHRINKFILE on a log file does not help in any way for the database you are shrinking. You can use it to make room for other DBs on the drive, but it will not make room for the current database as it can only remove space that is reusable. That means that any space freed up by it could have been used for your transaction anyway.