My google fu is weak today.

My work is deploying a new application using MSSQL 2005 (SP1). On our database server we have a 36GB system drive, and a 220GB drive for SQL. When I logged on to the server yesterday it had 0 free space on the C: drive. After a reboot it was back to 26GB. As of right now there is 1.5MB free. Searching the drive shows nothing, ~6GB of files total (including the 2GB page file). SQL is installed to the D: drive, as well as all the databases.

So it appears that something is taking up hard drive space, and never releasing it. This is the first time I've ever worked with SQL2005, having only had experience with MYSQL on my personal webserver, so I am really in the dark. Has anyone ever seen this before? Any ideas? SQL related, or Windows?

Thanks in advance for all the help you guys can give!

Unemptied trash?

I had a C: drive like that. The Admin had been updating various stuff and deleted various installers and obsolete folders, never bothering to empty the trash when he was done. As I recall now, It wasn't just the admin account, but that of a couple of other transients as well.

In Unix-land, this is almost invariably caused by one of two things. The first is that you mounted a filesystem at a mountpoint that already contained data, and the new filesystem is hiding it. While it's possible to mount a filesystem as a subdirectory under Windows, virtually no one does it, so this is terribly unlikely. The other, more common, issue, under Unix, is that a program has a handle on a file and then the file gets deleted from the filesystem while the program still has its filehandle open. In effect, this removes the file from being listed in any directory, but it doesn't free the disk space until the program closes the filehandle (or exits). I don't know if Windows works similarly, but it might be worth checking into. You could reboot the system to make sure. (Somehow I doubt this is it, since Windows gives you access errors when you try to open the same file twice, but what do I know?)

You might also want to try some sort of disk usage utility. It might point you in the right direction. I use Scanner.

How is the swapfile setup on the machine? If it's set to allow windows to adjust it, maybe something is causing it to grow out of control for some reason.

Swapfile is a possibility - if it's a new install then it might be hidden in the Explorer view.

One other interesting thing with SQL Server 2005 (but not relevant here) - a maintenance cleanup plan set to remove old database backups will not recursively remove databases when the backups are set to be saved in sub-folders. This is different to the 2000 behaviour and was only fixed in the most recent service pack.

The swapfile was set to 2-4GB, and when viewing system files I never saw it anything but 2GB.

It looks like the problem was with one of our sql databases. A 50MB test database was creating a 178GB log file on the D: drive of the system. Setting the recovery model to "Simple" and making sure "Auto Shrink" was turned on fixed it(along with a manual shrink). Took the log file down to 23MB. So it looks like it was using the system drive while creating/updating that outrageous log file. Definitely something messed up there. Fortunately it was related to just the one database, which will be deleted this week as we get ready to load actual data to go live with.

Thanks again everyone!

Sounds like quotas to me. Check your quota entries for the C: drive and make yourself an exception to quota limits. Windows reports how much free space you left in YOUR quota, not how much the disk has left.