Hi,
A quick follow-up for other folks with a similar problem. I've resolved the issue with our LDF file.
Chances are, if you're having to expand your LDF file and you're already taking regular transaction log backups (as we were), you've got an open transaction that is locking a table and preventing other queries from completing.
If you are noticing your LDF file beginning to grow out of control, run the following command WHILE the LDF file is growing:
DBCC OPENTRAN('VIM_VCDB')
If your situation is like ours, you'll definitely see an open transaction. In our case, the "Past Day Stats RollupVIM_VCDB" job was trying to delete old data and the query escalated to a full table lock. The result was that any queries that updated values in that locked table were being written to the log but were waiting for the table lock to be released so they could run. Virtual Center is SO chatty that it's actually possible for it to generate tens of gigabytes of queued transactions (especially in our setup).
In our case, the LDF file size was too small to allow all of these transactions to queue up while this DELETE statement executed, resulting in a full transaction log, and all queries failing. Then, 30 minutes later, the same job would kick off, only this time it had potentially even more rows than before to have to purge, meaning that every time this job fails to complete it's worse the next time it has to run.
SOLUTIONS
Follow the advice given to me and expand the max size that your LDF file can grow to. In our case, I needed a 25 Gigabyte !!!!! file and a full 18 minutes for the DELETE statement to run. This may not be ideal.
Turn off Virtual Center services and then go into SQL Server and manually kick off the "Past Day Stats RollupVIM_VCDB" job. I have not tested this, but because your Virtual Center instance isn't running queries against the database, I would imagine that the LDF file will not grow the way it was for us. (This was my Plan B in the event that (1) above didn't work.)
If you're comfortable with SQL Server, you might prefer to go into the "Past Day Stats RollupVIM_VCDB" job and see what Step 2 is doing. From there, you could write a query that only purges some of the flagged data rather than trying to purge it all in one go. (This was my Plan C in the event that both of the other two optionsfailed to work.)
In our case, once the purge finished everything calmed down. I shrunk the logfile and reset it to 500 Megabytes max, instead of the totally ridiculous 25 Gigabytes it has previously been set to.
Incidentally, the number of transactions generated during the 18 minute run of that DELETE statement amounted to 23 Gigabytes of data. Which raises one final comment from me. After running a trace on the SQL being run against this database, I noticed such goofy things as DELETING a row and INSERTING an exact copy of it again. Someoneat VMWare needs to take their dev team out behind the wood-shed and give them a good beating. An UPDATE statment would be better (and far easier on the log) and better still would be some basic checks to see if the data even needs to be updated (in the several cases I reviewed, things like IP address data was being deleted and then inserted -- we use static IPs in our datacenter) so a quick check on the Virtual Center side would prevent ALL of this chatter against our database server.We're getting between 20 and 50 queries PER SECOND from Virtual Center. In my estimation, you could reduce that chatter by 80% pretty easily.
I'm happy that I've solved the problem, and I'm hopeful that this potential issue will go away in a future release of the software.
within 30 minutes it starts growing explosively and fills up. Transaction log backups become spotty for the next couple of runs, until things are OK again just in time for the LDF to fill up again.
