I'm not a DBA and have almost zero knowledge of databases.

One database on my SQL Server system would not start. I was getting an error like:

FCB::Open failed: Could not open file XXXXXX
for file number 2.  OS error: 5(Access is denied.)

Since this database is simply for testing, I dropped it and tried to recreate it.

The process I followed is: drop database, stop mssql service, delete any remaining data files, start service.

Worked fine for a while, but after a reboot I'm back to the same issue.

When I inspected this further, I can see that I have a 'ghost' reference to the file in sys.master_files. In other words, I drop the database and I still see a reference to this file here.

I thought to just delete the reference like this:

DELETE FROM sys.master_files where database_id = 6

But I get this:

[Code: 259, SQL State: S1000]  Ad hoc updates to system catalogs are not allowed.

I did check permissions etc, and the first error was nothing to do with that.

If I could just find a way to delete this reference I'm sure the problem would be solved.

  • A few things, 1) we'd really need an output of sys.master_files 2) stopping sql server and removing files is a sure fire was to shoot yourself in the foot. If there is a lock on the file, it's in use and you shouldn't touch it. 3) deleting a database happens in two different steps, 1 is removing the database from the instance, the second is deleting the OS files. Failure to delete an OS file does not stop the database from being removed from the instance with associated data. – Sean Gallardy Feb 11 at 18:59
  • Unlikely that we are going to figure out how this happened, but you can restore or rebuild the master database. docs.microsoft.com/en-us/sql/relational-databases/databases/… – Tony Hinkle Feb 12 at 1:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.