Why Does the Transaction Log Keep Growing or Run Out of Space?
My client is running SQL Server 2005 x64 Standard on a beefy server with 16 GB of RAM, and separate drives for data, log and temp. Daily backups include log backups.
Their accounting system (Accpac) database is around 45 GB and has been running on this server for the past 3 years with no problem.
About 3 or 4 months ago, during weekly maintenance plans that do an integrity check, index reorganizations, and update statistics, the log file ballooned to twice the size of the database and the job failed due to a disk full error.
At first, I thought the backups were not running so I dumped the log. I know I should not do this, but I didn’t really have any choice. Then, I began monitoring the nightly backups. All was good until the next index reorganization occurred. The same problem occurred with the log running out of space and I realized it was happening during this task.
I moved the log files to the db disk and the db files to the log disk since the db disk was larger. I wanted to see if I could at least get the job to finish by giving it more room. Now it ballooned to over 3 times the size of the database and again filled up the disk.
For now, I have just turned off the weekly index reorganize task which is not really a good long term solution.
I'm just using the reorg index task that is in the maintenance plan designer GUI. I was going to take a look at Ola Hallengren's scripts and some other stuff but wanted to see if there was possibly a simple explanation for this.
Also, I do have "compact large objects" checked but assuming this is for blob data? I don’t believe there are any BLOBs in this database.
Oh, I'm a developer wearing a DBA hat, so be gentle with me.
I forgot to mention recovery model is "bulk" vs. "full" - I believe this was set by the package vendor.