Deleting offline database in SQL Server doesn’t delete database files

If you drop the database in SQL Server that is offline or any one of its files is offline when the database is dropped, data and log files are not deleted.
You have to delete them manually after dropping the offline database to free the disk space, or to create a new database with the same file names as the deleted offline database.

If you try to create the database with the same name as the dropped offline database, and you didn't manually delete the files after dropping the offline database you may receive the following error:

Cannot create file 'C:\...\file_name' because it already exists. Change the file path or the file name, and retry the operation. CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 5170)

file already exists create database in sql server error 5170