0

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.

New contributor
Aditya K is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • 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

Aditya K is a new contributor. Be nice, and check out our Code of Conduct.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.