One of our customers reported their Veeam backups were failing. We determined the cause to be the vCenter services were stopped and would not restart. The vCenter issue was a result of the SQL Express database having grown to its 10GB maximum size. We were able to get the vCenter services running temporarily by purging performance data from the database using the procedure at http://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1007453. [more]
This procedure removed enough data to get the services running, but didn’t reduce the overall size of the database significantly. I found a VMware SQL stored procedure named “dbo.cleanup_events_tasks_proc” that reduced the size of the database by 60%. After a couple of shrink file operations, the database and the vCenter services were up and running.
However the Veeam backups failed yet again the next night. While the Veeam errors indicated that the vCenter services were again offline, this time it was because the virtual disk containing the SQL Server Express vCenter database was completely full. The transaction log for the vCenter database had bloated to 24GB and filled up the disk. This was confusing initially because I had checked the recovery model of the database prior to running the stored procedure to make sure it was set to “Simple” to prevent this very issue.
With SQL Server the growth of the transaction log is directly proportional to amount of “work” that SQL Server has to perform between BEGIN TRANSACTION and COMMIT TRANSACTION commands. Certain SQL Server commands (insert, update, and delete) are always wrapped in implicit transactions. But some bulk operation transactions can be executed with explicit BEGIN/END TRANSACTION commands to control roll back. The stored procedure that I ran wraps a potentially large batch purge process in a SQL transaction that enables the entire process to be rolled back in the event of a failure. In this case, the lengthy stored procedure resulted in a ridiculously huge transaction log. Lesson learned is that “Simple” recovery model doesn’t guarantee the transaction logs will always be a manageable size.