some time ago i faced an issue with the vcenter db transaction log, because it became full every 6 hours (at fixed hours).
In the windows event log this message appeared:
The transaction log for database 'VIM_VCDB' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
If i checked the log_reuse_wait_desc column in sys.databases just after the event log happened, i noticed that the transaction log truncation was delayed waiting for a checkpoint. This information didn't help so much ... then, i made some research to learn the way the transaction log works.
The MS SQL database have configured the simple recovery model, then transaction log not should be so big as when using the full recovery mode.
In simple mode the database logs the active transactions in the transaction log, and after that, it frees space (log truncation) in the log. This truncation really don't make smaller the physical transaction log, It frees space for reuse by the transaction log. To be able to make smaller the physical transaction log, the log must be shrinked. Anyway, shrinking the file don't help.
I found this discussion in the vmware communities. It helped me to go in the correct direction.
The transaction log was getting full every time the dbo.cleanup_events_tasks_proc stored procedure was executed.
(To know what stored procedure was filling the transaction log, i used this sql query)
Every 6 hours this procedure tries to delete the data from the
vpx_task tables based in the "database retention policy" specified in the "vCenter server settings". When i activated the "database retention policy" (180 days), the transaction log issue started to happen.
I checked the
vpx_task tables and i noticed that this tables had entries/rows since 2012.
Then, i increased temporally the transaction log size from 500 MB to 2000 MB and executed manually the dbo.cleanup_events_tasks_proc. It was executed correctly (it deleted the entries older than 180 days in the
vpx_task tables) .
Finally, i configured the transaction log size back to 500 MB.
And no more transaction log full messages in the windows event log