Avatar of bill_faulk
bill_faulk
Flag for United States of America asked on

SQL 2008 - Log grows and won't truncate with BULK LOGGED mode, have to recreate log to free space

This is 64 bit SQL 2008 standard 10.0.2531 running in Windows 2008 R2 Standard 64 bit.

I have a database where mass insert or delete operations are often used. I set the recovery model to "Bulk-logged" and I'd expect that to avoid growing the log file significantly. That's not the case however. The log file grows and never releases all of the space used. Sometimes it won't shrink at all even though it is several gigabytes in size and there is no activity. Sometimes I load image data and that causes HUGE log files in the 100GB size range. This is BULK LOGGED recovery method being used  even after I set up a 200 record max transaction size in the scripts to try to avoid the issue.

If I set the recovery model to SIMPLE then the log will truncate after a backup. So, my workaround is to use the SIMPLE recovery model and keep enough free space for the log to grow. It's growing anyway in BULK LOGGED mode but won't truncate so that setting is rather pointless when it doesn't work.

In sys.databases the value for log_reuse_wait_desc is LOG_BACKUP even though the recovery method is bulk logged. This value does not change after a backup.

If I backup the transaction log with the truncate option selected it still does not truncate. Log_reuse_wait_desc is set to NOTHING. The log still won't shrink though and resets to LOG_BACKUP after a transaction.

I even set up a T-sql script to only process 200 records at a time but the log still grows. Once it grows it will not release the space.

If I truncate the database the log file also grows and won't truncate afterwards.

This never happened to me in SQL 2000 or SQL 2005. In SQL 2008 the log file isn't behaving as I would expect from past experience.

I have to detach the database, delete the log file, and reattach the database to have the log file reset to its original size. I even tried creating a second log file and emptying the first log file and it still wouldn't shrink.

I've used the "checkpoint" command before truncating or shrinking data files but that didn't make any difference.

No errors are shown with DBCC Checkdb or DBCC Checkcatalog,

I have tried changing to simple recovery model and the behavior is the same.

I have tried backing up the transaction log but it still won't shrink/truncate.

"DBCC Loginfo" shows 335 rows most of which are still status = 2 even after the log is backed up. I attached the output to this message. If I change the recovery model to simple they are set to zero except for one record and the log will truncate.

"DBCC Opentran" shows no open transactions.

I have tried using the "reorganize pages" and shrink-to size when shrinking log files but nothing happens.

Rebooting the database or server doesn't have an effect.

Nothing unexpected in the sql log. Nothing in the event logs in Windows for sql.

So, is bulk-logged broken? Is sql server assuming there is no log and not dealing with the fact that there is one anyway?

Is there some setting that can cause this to happen?

Can anyone think of something else to try to figure out the cause for the logs growing and not truncating when using the bulk logged recovery model?



loginfo.txt
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Anthony Perkins

8/22/2022 - Mon
Anthony Perkins

>>I'd expect that to avoid growing the log file significantly. That's not the case however.<<
What made you think that?

>>If I set the recovery model to SIMPLE then the log will truncate after a backup.<<
Simple Recovery Model does not truncate the log.

>>The log still won't shrink though and resets to LOG_BACKUP after a transaction.<<
It is not intended to shrink.

>>If I truncate the database the log file also grows and won't truncate afterwards.<<
Again, that is by design.

>>I have to detach the database, delete the log file<<
And now that is dangerous.  Don't do that (and especially if you do not have your resume updated).  You will end up corrupting your data.
bill_faulk

ASKER
>>Simple Recovery Model does not truncate the log
You seem to have read my comments to mean I expect the log to truncate automatically. This is not the case. I can successfully truncate the log in simple recovery mode. In bulk logged I could never ever truncate/shrink the log even after backing up the log.

I do not believe that not being able to truncate the log even after backing up the log is "by design".
bill_faulk

ASKER
Also, with bulk logged I'd expect some bulk operations to be "minimally logged" as opposed to fully logged. This is preferable when inserting millions of rows into a table. If it failed for some reason I could do it over again but with simple logging I could run out of disk space or reach the maximum log file size. It defeats the purpose if I can never shrink the log.

Also, I always have a backup before doing any think like detaching the database.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
bill_faulk

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Anthony Perkins

>>Originally I wanted to figure out why I could never truncate or shrink the log even after a log backup but I need to move on.<<
Fair enough.  Please close the thread by accepting your solution.