Sometime it happens on SQL server we can find MDF, LDF or NDF files which belong to no currently used DB. They are just consuming disc space. If there are more administrators and SQL server is quite big (more discs, lot of DBs) it could easily happen somebody's detached DB from the server and forgot to move / delete / archive its files.
But how could we know which files are still used and which of them are orphaned?
As a solution I've prepared PowerShell script which:
- connects to the SQL server and gather list of database files (the full path),
- goes trough the whole server (except drives which are excluded),
- compares DB files found on the file system to list of database files from SQL server and the files not found in the list are reported as files which could be deleted from the SQL server point of view.
I recommend to exclude "C" drive from the search because of many files / directories needed to be checked. You can also exclude e.g. drive dedicated for backups.