Disk Space Race Condition

Hah! This is one for the record books or something… I’m maintaining a production SQL Server 2000 host with about 20 databases on it. None of them are huge or anything, and the hard drive the data and backups live on has about 120 GB free at any given time… Last night during the backup rotation something happened in which the server created over it’s limit in temp files. I can’t tell exactly what was going on from a cursory look at it, (I’ll have to comb the logs more thoroughly) but some operation or set of operations make the temp directory exceed the 100+ GB of freespace that it had before it started the process. I found an old archive file containing 40 MB of data, which I deleted, and within 5 seconds 111 GB of free space opened back up. (That is not an exaggeration, 5 seconds.) Apparently whatever operation was waiting for space found enough in that 40 megabyte window and completed, freeing up the temp files… All I have to say is WTF! Who would have thought that could happen? The total amount data stored on the disk in database and log files is around 20 gigs, the hard drive itself is 300 Gigs, and there are various backup files laying around for the rotations to be able to keep two older backups of each database. So, that means that the simple operation of backing up 20 gigs of data triggered 110 - 120 gigs of temp files and caused the server to deadlock the operation (with good reason!) I guess that means that you need a LOT more space on the drive than your production data and backups… I mean there was still 500% of the data size left on the drive before the operation began, you’d think that would do it. Oh well. Lesson learned.