Recently was working on a production instance of Windows Server 2003 that hosts one of our key installations of WebWatchBot to monitor our servers.  This instance is on a virtual machine, using Microsoft’s Virtual Server 2005 R2, that was initially created with 16GB of disk space.  For anyone not familiar with Virtual Server, once you allocate disk space for your virtual machine, one cannot expand that disk.  Instead, you have two options: add an additional logical disk or start over and copy from one VM to another.  There may be an easier way, but I’ve yet to find it.

The problem was that the disk was running out of space: only 200MB left.  Since expanding the disk was not an easy venture and finding the root cause of the problem was the best way to go about solving the problem, I installed and ran a great open source product named WinDirStat to map the disk and pinpoint the directories and files taking up the most space.

To my surprise (though I should not have been surprised), the problem was one gigantic file taking up 8GB of space: the SQL Server 2005 database transaction log file for the database used by WebWatchBot for monitoring.  The database itself only occupied 200MB of space.  What was even more of a surprise, after some digging around the Internet and SQL Server Books Online, was that this unlimited and unrestricted growth is mostly by design.  Is that possible?  I’m sure that I could have configured the transaction log to have restricted growth when the database was created; however, it did not enter my mind that the transaction log would grow so huge.  I assumed that the transaction log would keep itself in check.  No so.

The final resolution was to backup the database, reduce the size of the transaction log, and monitor the file for future growth.  Complete instructions on reducing the transaction log’s size here: http://www.exclamationsoft.com/exclamationsoft/support/kb/webwatchbot-sql-server-database-files.asp

Another good article on the transaction log: Dealing with a large transaction log file