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.

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

The error tells you to look at the `log_reuse_wait_desc` column. What was in there? — Damien_The_Unbeliever, Feb 25 '12 at 10:50
The error message tells you exactly what to do. Did you actually read it? — usr, Feb 25 '12 at 16:18

score 30 · Answer 1 · answered Aug 09 '13 at 08:09

30

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>

answered Aug 09 '13 at 08:09

Alex Filipovici

31,789
6
54
78

1

Thank you! This helped me control my WUG DB. My errors/discards table is the one I sacrificed: I need a month's worth of ping and interface utilization monitoring, but only a week or 2 of errors/discards. My "YourTable" is "dbo.StatisticalInterfaceErrorsAndDiscards" and my "" is dPollTime < '01-31-2015'" (updating that date for 2 weeks ago). I run this weekly. Works great. – Smithers Apr 06 '15 at 14:47
2

This is the top answer as far as I'm concerned. – Zonus Apr 15 '16 at 02:25

score 17 · Answer 2 · answered Mar 10 '14 at 08:00

17

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 .

answered Mar 10 '14 at 08:00

nitesh.kodle123

1,041
4
11
23

How do you exactly increase the size of disk? Most people don't use VM for their database servers! – AaA Jul 27 '15 at 11:10
2

I had the same problem. Increasing the disk size worked. I also did a 'shrink' operation on the logs, this gave me an extra 85 GB. – Oak Oct 14 '15 at 08:26

score 15 · Accepted Answer · answered Feb 25 '12 at 13:29

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.

I ran into this on a throwaway test database - tried to do a transaction log backup but did not have enough hard drive space. So, instead I was able to go into the db properties and change the recovery model to simple as suggested to work around the issue - thanks — imjosh, Aug 01 '18 at 16:09

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

3 Answers3

Linked