SQL Server doesn't delete all database files, when the database is deleted?

0

I run SQL Server using Docker with image mcr.microsoft.com/mssql/server:latest.

My problem is that, if I delete a database as follows:

ALTER DATABASE [ZapDB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
DROP DATABASE [ZapDB];

then files remain in the SQL Server's filesystem, which prevents a new database with the same name to be created:

System.Exception: Could not resolve a service of type 'ZapProxyServer.Services.ZapDbContext' for the parameter 'zapDbContext' of method 'Configure' on type 'ZapProxyServer.Startup'.

Microsoft.Data.SqlClient.SqlException (0x80131904): Cannot create file '/var/opt/mssql/data/ZapDB.mdf' 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.

How can I tell the SQL Server to purge the database from the filesystem, so that all traces of it are gone? Otherwise, I have to manually delete these files...

sql-server
database
asked on Stack Overflow Jan 10, 2020 by Shuzheng • edited Jan 10, 2020 by marc_s

1 Answer

3

SQL Server no longer accessing the files when you set database offline.

Instead of setting database offline switch it to single user mode and then drop.

answered on Stack Overflow Jan 10, 2020 by Piotr

User contributions licensed under CC BY-SA 3.0