read_only filegroup

Question

read_only filegroup

deepforest

SSCommitted

Points: 1780
More actions
February 9, 2012 at 8:25 am

#250455

Hi,
I have a filegroup which is set to read-only. I need to set it back to read-write. When I try to modify it, it errors out with: 'An error occurred while processing 'AltFile' metadata for database...'
After looking at sys.master_files, the filegroup has all redo_xxxx_lsn values set. The redo_start_lsn and redo_target_lsn is the same values, so theoretically, the filegroup is ok, but it still prevents me from changing the read-only mode.
Any thoughts?
Thanks.
March 6, 2012 at 3:09 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Anything in the error log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

deepforest SSCommitted Points: 1780 More actions · Answer 2

Nothing suspicious, but obviously it happened during some past restore. I'm trying to find some older full backup and restore the FG and see if it's ok. But still, I'd like to figure out how this happened in first place.

deepforest SSCommitted Points: 1780 More actions · Answer 3

No it's not compressed. It was completely different issue. The particular FG was pointing to a non-existing file. I talked to MS and the solution they came up with was to keep adding filegroups until the non-existing file record is overwritten. It worked.

Paul Duffett Mr or Mrs. 500 Points: 536 More actions · Answer 4

Hi,

I just wanted to add something to this topic as I had the same error but the fix was slightly different, although adding files to the database was the key.

The error I received was:

An error occurred while processing 'AltFile' metadata for database id 8, file id 3, and transaction='alterdb'.

Upon reviewing the sys.sysfiles for the database (id=8) it was obvious that there was no fileid=3. There was a 1, 2, 4, 5, 6 but no 3.

There were two filegroups: PRIMARY had 1 file, with a fileid=1, and the SECONDARY had three files, fileid=4,5 & 6.

I suspect there was a second file in the PRIMARY filegroup at some stage with a fileid=3 which was removed, thus leaving a hole in the sequence.

The solution was to add a file to the PRIMARY filegroup, confirm that the file was created with a fileid=3 and re-run the ALTER DATABASE command, this time it worked fine.

Just wanted to share that with the community.

Thanks

Paul