My online baseball game, CSFBL, is a data hog to the tune of about 100GB. Yes, 100GB. That’s how much disk space it takes to store the play-by-play results of some 5 million baseball games (over 2,500 seasons) along with the related player data, historical statisics, and other fun stuff.
The server that powers this beast has three 146GB SCSI drives in a RAID5 configuration. That’s about 250GB of available disk space. Excluding data requirements for such necessities like Windows, SQL Server, tempdb files, web files, and the like, we typically have between 50GB and 100GB of free disk space.
Of course, backing up that database is another story entirely, and if I’m not diligent in keeping the disk clean (by deleting old and unneeded data files), the server can quickly run out of space. This has happened from time to time over the past few years, but a quick cleanup resolves the issue.
Over the weekend I decided to take on major maintenance, defragmenting all SQL indexes, cleaning up old data, removing unused applications, and similar fun weekend chores. During this, I checked the level of fragmentation on the SQL database file.
Over 40,000 file fragments.
Whoa! How long does it take to defragment that? About two days. Of course, a lot of that time was spent first defragmenting other files to make room for one big file. The built-in Windows defragger didn’t do the trick at all (it fails miserably unless you have gobs of free disk space or no large files to defragment). What did do the trick was an evaluation version of Raxco‘s PerfectDisk. Fortunately for my wallet, the eval version is full-featured for 30 days, and since my defrag needs are done, I don’t have to buy it — at least, not until I have to defrag the database again.
Then again, I’m planning ahead this time. I set the SQL database to have about 30GB of free space, so the only fragmentation should be internal, not external. By the time I need to worry about a fragmented SQL data file again, there will be another 1.5 million baseball games simulated, and hopefully a little more ad revenue coming in. 😉