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?