Apologies if this has been posted before, but I keeping coming across excessive log growth on my reporting databases that are in SIMPLE recovery (can get to 80GB which is unusual for these databases).
These databases are large monthly reporting databases that perform many bulk inserts (via our loader) into the database throughout the day. These databases are large in size as a result for example this month’s being around 200GB so far.
Now on our 2005 server transaction logs were never a problem. However, we have recently taken the plunge and migrated to SQL 2012 with this reporting server.
Ever since then we have noticed that index rebuild \ reorganise seems to take much more log space than compared to 2005 (even when disabling the loader and ensuring nothing is reading \ writing whilst this is taking place).
However, what has thrown me is that on occasion, we can get log growth for which seems no reason.
When looking into this when it is happening the log_reuse_wait_desc on sys.databases says either:
ACTIVE_BACKUP_OR_RESTORE or LOG BACKUP. However, no backups are taking place on the server at all. And I confirm this by running:
database_name = DB_NAME(database_id),
WHERE command IN (N'BACKUP DATABASE', N'BACKUP LOG');
I also check that there are no large uncommitted transactions and find none. I’m also a bit confused why it would say LOG_BACKUP in the wait description when the database is in SIMPLE recovery.
Would anyone be able to point me in right direction on what root cause could possibly be here? Or what SQL could be doing in the backend (perhaps a checkpoint of some sort?)
Any help would be appreciated.
(BTW the quick fix I found when this happens is to change the recovery model of the database to FULL, then back to SIMPLE and then shrink log, but I’m really no keen on doing this everytime!!)