3 Incorporated comments
source | link

First of all please forgive my ignorance is this is a silly question. I'm not a DBA and have almost zero knowledge of databases.

One database on my MS-SQLSQL 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.).

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

Since this DBdatabase 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 the sys.master_files.

 sys.master_files. In other words, I drop the database and I still see a reference to this file here.

So I thought to just delete the reference like this:

DELETE FROM sys.master_files where database_id = 6

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.

[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.

How doIf I fixcould just find a way to delete this? reference I'm sure the problem would be solved.

First of all please forgive my ignorance is this is a silly question. I'm not a DBA and have almost zero knowledge of databases.

One database on my MS-SQL 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 DB is simply for testing, I dropped it and tried to recreate it. 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 the sys.master_files.

  In other words, I drop the database and I still see a reference to this file here.

So 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.

How do I fix this?

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.

2 edited tags
| link
1
source | link

MS SQL : 'Ghost' file reference in sys.master_files

First of all please forgive my ignorance is this is a silly question. I'm not a DBA and have almost zero knowledge of databases.

One database on my MS-SQL 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 DB is simply for testing, I dropped it and tried to recreate it. 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 the sys.master_files.

In other words, I drop the database and I still see a reference to this file here.

So 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.

How do I fix this?