Cannot delete SQLServer2005 data or log files

Question

I was trying to restore a backup but I kept getting OS error 32 - cannot delete because in use.

Couldn't figure out what was locking the data and log files - so I rebooted the machine, stopped the service but still no luck.

Then I deleted the DB but data and log files are still there.

Rebooted again - tried to delete them but looks like there's no thing to do, windows doesn't let me.

Any idea?

Answer 1

You should be able to detach (not delete) the database and then move or remove the data files.

Microsoft KB.

Answer 2

Use Process Explorer, a Microsoft tool that will tell you which process currently has a file locked, which will tell you what you need to stop/close before you can delete a file.

Here's a tutorial on finding out who has a file locked:

http://windowsxp.mvps.org/processlock.htm

Answer 3

You deleted the DB's in SSMS but the files are still there? (shouldn't need to detach anymore if you "deleted" the entries" You probably checked the "Keep data files" checkbox

but I imagine a reboot should allow you to delete the actual .mdf and .ldf files If not, try booting into Safe mode and delete the actual files then

Answer 4

Antivirus software is one that catches DBAs - the MDF/LDF files are pretty big, and I've seen cases where the AV software grabs a lock on the files to scan them, but it takes forever due to the file size. You might try stopping your antivirus service.

Another one is backup programs - if you're using file-based backups (like Backup Exec or NetBackup) they may be trying to run a file backup at the time. Check for any backup software services, stop those, and then try to delete the files.

You might also have multiple instances of SQL Server installed, and this might belong to another instance, not the one you were working with.