This is a quick post about an issue we have encountered a couple of times when drop a database and one or more files appear to be left over for some time afterwards. This time, we found the culprit! 
Essentially, dropping any database is a relatively fast process – unless of course you have elected to delete the backup history; and there’s a lot of it. However, we’ve noticed that sometimes, 1 or more of the database files seems to hang a round for a bit afterwards; and that’s not something that’s supposed to happen! As I understand it, the only reason that 1 or more files hang around after you have dropped the database is that something is holding the file open, preventing the deletion from the file system; and there are only 3 possible culprits: –
- SQL Server
- Anti-Virus (I know that it should be excluding the data files etc in SQL Server; and it is, but at this point I did not want to make any assumptions).
- Diskeeper 2010
After cheeking with our anti-virus administrator, I confirmed that the anti-virus software was indeed excluding these database files, then a quick check on Google showed me that either no-one else was experiencing the problem or no-one else had noticed it yet.
So I start looking at Diskeeper, the best way for me to confirm that the Diskeeper service had indeed got the file open was to stop the service and see if the file disappears. Strangely enough, it did.
There is one thing to note though – this is not something that happens all the time, but we have seen it happening from time to time. Reproducing the issue takes a fair amount of time and effort; and a lot patience!
Phil Jackson's Blog 