It's useful to periodically check if there are any old database files that can be deleted from a SQL Server. Usually database files will be deleted along with the database,
however this is not always the case. For instance if a database is set to ‘offline’ before deleting then its data and log files will not be deleted.
Some databases will have multiple data and log files so it’s not always obvious which ones are in use (though checking the file time stamp can help).
To get a complete list of all data and log files used by all the databases on a server then run the following query :
SELECT name, physical_name, type_desc
FROM sys.master_files
If you find a file that’s not listed then it’s a good candidate for deletion.
You can also find out what files are used by the *
current* database by running the following query :
SELECT name, physical_name, type_desc
FROM sys.database_files
The same information can be obtained from SQL Server Management Studio by right clicking the appropriate database and selecting 'Properties' and then 'Files'.