February 7, 2014 at 9:59 am
Hello All
I have what I suppose is not a very common situation
A large database near 1TB, 6 filegroups, one defunct/offline filegroup and corruption in the main file,
so far everything is working "fine" but i know that I have a potential disaster is I do not fix that database, since a check of the database integrity shows several errors, I want to recover that database and at the same time be able to remove the offline filegroup, those who have seen it will know that MS was kind enough as to leave you without any option when a FG is like that, it will remain forever wasting space
So I am trying to move all the data from that database into a new clean one, I am thinking about using replication or an SSIS component to move the data, anyone have better ideas?
the machine I have for that is medium size 16 cores, 96gb, MS Sql server 2008 r2
Thanks in advance
February 7, 2014 at 10:55 am
Can you not just drop the filegroup? It should work, though may need a couple other steps first.
What's the corruption-related errors? Might be an option that doesn't involve recreating the entire DB.
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
February 7, 2014 at 11:03 am
I would love to drop that filegroup but on the current state everything I have tried returns an errors saying that it is not possible because the filegroup is offline, if you know a way to deal with that I will really appreciate it
for the corruption I have another couple options, but since I also want to partition that database and fix a few other things it is a nice option to have a "clean" to create everything in there
February 7, 2014 at 11:15 am
ricardo_chicas (2/7/2014)
I would love to drop that filegroup but on the current state everything I have tried returns an errors saying that it is not possible because the filegroup is offline, if you know a way to deal with that I will really appreciate it
I might, with more information including the exact status of the filegroup and files and the exact error messages
for the corruption I have another couple options, but since I also want to partition that database and fix a few other things it is a nice option to have a "clean" to create everything in there
Personally I wouldn't enjoy spending a weekend recreating a TB DB unless absolutely necessary, but if that's the chosen path... I'd probably use BCP. BCP out the tables to disk, drop the DB, recreate the structures and BCP in. Makes it nice and modular so if one thing fails that one thing can be rerun
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
February 7, 2014 at 11:29 am
This one I try to use it as something to learn how to move large amounts of data, so I do not mind spending some time doing it, will try the bcp path, thanks
for the filegroup it had a separate file, was set offline, it is possible to remove the filegroup but not the physical file, when I try to do that I get this:
Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup 'FG_lost' because the filegroup is not online.
February 7, 2014 at 11:53 am
And if you try to drop the filegroup? An error that it's not empty?
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
February 7, 2014 at 11:57 am
exactly, that is the error
I am reading about a way to do it changing the system tables ( something that I will try in a safe test environment )
http://www.sqlnotes.info/2013/05/09/bring-offline-file-online-modify-sql-server-metadata/
February 7, 2014 at 12:02 pm
No, no, no, no, don't do that.
What's the exact state_desc of the file? (sys.database_files)
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
February 7, 2014 at 12:06 pm
the state is 7: Defunct
February 7, 2014 at 1:04 pm
ALTER DATABASE <database name>
MODIFY FILE (Name = <file name>, OFFLINE);
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
February 7, 2014 at 1:59 pm
that got me this message:
Msg 5057, Level 16, State 3, Line 1
Cannot add, remove, or modify file 'offline_data' because it is offline.
February 8, 2014 at 1:02 am
Hmmmm....
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply