On a couple of our development SQL Server machines, we've run out of space fairly often. Lots of databases are being created frequently by multiple different developers, sometimes production backups are restored, or sample data is loaded. Lots of databases are dropped, too. But I believe there may be some bug in the drop database command, such that sometimes, it drops the databases, but the mdf and ndf files are left there.
This is not something I've been able to reproduce, but I've run into it enough that I have a routine that goes like this:
1. Run out of space on the development server
2. Check on the drive that's low on space for files over a certain size - usually about 5o gigs. In our environment, these are almost always SQL Server database files.
3. Use the sp_msforeachdb stored procedure to check which, if any, of these files is associated with an existing database. If you have a lot of databases, this is LOTS easier than running sp_helpdb for each individual database
exec sp_msforeachdb
'
SELECT DatabaseName = ''?'', * FROM ?.dbo.sysfiles
where filename like ''%InsertFileNameHere%''
'
For those that are NOT associated with a current database, you can delete them and clear up some space. Be careful here, and do any double-checking you feel is necessary.
