ALTER DB FileGroup to READWRITE from READONLY
-
In SQL 2005, I am trying to change a filegroup that is currently READ ONLY back to READWRITE with an ALTER DATABASE command. I am getting the following error:
An error occurred while processing 'AltFile' metadata for database id 6 file id 3.
I have not found anything helpful on the net to resolve this issue. ANy ideas would be much appreciated.
-
Have you checked to make sure the metadata for that file matches the actual file?
Like, if it has the right drive and directory in sys.files, or not?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Yes. In sys.database_files and sys.sysfiles, the directory and filename are correct.
One thing I did notice in looking at the view sys.database_files is that the filegroup I am trying to alter back to readwrite has a value in the file_guid column. The other filegroups show NULL in this column. Not sure if that has any bearing on the issue.
-
This method may be a little longer. Have you considered creating a new filegroup that is readwrite and then moving tables and indexes from the readonly filegroup to the new readwrite filegroup?
Another question, is the readonly filegroup on a compressed ntfs volume? If the volume is compressed you would have to decompress it first - you might be able to see an error message in the ERRORLOG for this.
And lastly, have you tried a DBCC CheckDB?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Thank you. I'd rather not have to go the route of creating a new filegroup and emptying the other into it.
We use the filegroup in question for archived data and it turns out there is some more data that needs to be archived for a given fiscal year, hence the desire to make it read/write again.
I'll try the checkdb.
-
Chris Patton (8/26/2009)
Thank you. I'd rather not have to go the route of creating a new filegroup and emptying the other into it.We use the filegroup in question for archived data and it turns out there is some more data that needs to be archived for a given fiscal year, hence the desire to make it read/write again.
I'll try the checkdb.
Seeing that this is for archival purposes and the need has come up to add to the archive - have you considered partitioning the table(s) using a sliding window and read/write and readonly files in the group for the partition?
Just a thought - in case this type of thing comes up again.
Lastly, have you thought of going to Vegas? Seems you might be lucky after hitting all 7's on your last post in this topic
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply