March 10, 2016 at 5:52 pm
Hello all,
I am checking to see if this is even something that can be done.
I have a non-prod db that was restored from production some time ago.
The database in production has two filegroups (Primary, FileStream)
Well, there was a backup done of the primary filegroup only and that was then used for the non-prod restore, mostly due to size.
Now it is being requested to clean up some obsolete objects, and one of the objects was the Table tied to the Filestream.
I can't drop the able due to the filegroup being 'defunct' / Offline
I can't drop the filegroup because the filegroup isn't empty.
I have searched all over online looking for a supported method of clearing up essentially this meta data but cannot find a working solution so I figured I would ask here.
Any advice is appreciated.
Thanks,
Lee
March 10, 2016 at 9:56 pm
This was removed by the editor as SPAM
March 11, 2016 at 7:15 am
Hello and thanks for the reply.
The files in that particular filegroup are already listed as defunct, attempting to remove the files yields the following error
Cannot add, remove, or modify a file in filegroup 'FileStream' because the filegroup is not online. (Microsoft SQL Server, Error: 5056)
I can't bring the FG online for obvious reasons.
Thus my issue which I cannot figure out.
Hopefully I am explaining this situation in a clear manner. I feel like it is a bit of a catch 22 since I can't get rid of the metadata of the defult files / filegroup and cannot drop the table because the table is part of an offline filegroup.
March 13, 2016 at 9:02 pm
This was removed by the editor as SPAM
March 14, 2016 at 2:51 am
Last time I saw this it did indeed turn into a catch-22 (worse in the other case as it was a production DB and the missing filegroup was completely missing)
As far as I'm aware, you can't remove the filegroup when it's in that state and you can't drop the objects on those filegroups.
If it's just a case of cleanup, then leave the objects alone and explain to whoever asked you to remove them that they can't be dropped. If there's some pressing need to drop them, you'll have to restore the missing filegroups first.
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
March 14, 2016 at 4:25 am
The obvious answer, which you indicated you might not be able to do because of size, would be to restore the entire database (not just the primary filegroup) down to non production and then clean up the objects.
I'm assuming that this cleanup is only happening in non-prod, not production?
March 14, 2016 at 5:14 am
This was removed by the editor as SPAM
March 14, 2016 at 5:34 am
prettsons (3/14/2016)
Add a new file into filegroup then empty the first file by DBCC SHRINKFILE.
You can't do that if the filegroup is offline, and besides, the OP stated that the offline filegroup is a filestream filegroup, which doesn't contain data files and can't be shrunk.
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
March 14, 2016 at 10:50 am
I learned of all these caveats the hard way. We have a large partitioned table that we only need 3 months of for Dev restores. What a freakin' nightmare that turned out to be. To make matters worse, the partition function and scheme don't allow changes when you have missing file groups. I still don't have a decent way to do it and have had to do a brute force copy of the data. Thank goodness the table resides in it's own database.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 14, 2016 at 2:50 pm
Sorry I have been in an all day meeting...
Yes, I am in the simplest terms stuck in a catch 22, I can find no way of removing the table as it resides in an offline filegroup (which is FileStream based).
I cannot remove the files for the same reason, I cannot remove the FG because it is not empty.
The database in production is 7 TB in size which is why we have the primary file group (Copy Only) backups going to refresh the lower environments.
To get around this, I did have an actual full restore done on another server which wasn't being used at this time so I went through the process of dropping the table, then the files and then the file group...and once that was complete, I restored that 'out of date' copy and did a compare and sync against it using redgate compare.
SO...I got around the problem, but didn't solve it per say and I don't think I would have found a working supported solution.
Thanks for all the replies, I still have the copy of the database so if there are some things to still try feel free to throw them my way.
Lee
March 14, 2016 at 8:52 pm
This was removed by the editor as SPAM
March 15, 2016 at 3:46 am
Leeland (3/14/2016)
To get around this, I did have an actual full restore done on another server which wasn't being used at this time so I went through the process of dropping the table, then the files and then the file group...and once that was complete, I restored that 'out of date' copy and did a compare and sync against it using redgate compare.SO...I got around the problem, but didn't solve it per say and I don't think I would have found a working supported solution.
I would have to disagree. You did find a working supported solution. Looking for a solution where you only use the primary file group would not be a supported solution.
I don't think there is a solution only using the primary file group unless the objects you're cleaning up are only on the primary file group.
March 15, 2016 at 8:16 am
Brandie Tarvin (3/15/2016)
Leeland (3/14/2016)
To get around this, I did have an actual full restore done on another server which wasn't being used at this time so I went through the process of dropping the table, then the files and then the file group...and once that was complete, I restored that 'out of date' copy and did a compare and sync against it using redgate compare.SO...I got around the problem, but didn't solve it per say and I don't think I would have found a working supported solution.
I would have to disagree. You did find a working supported solution. Looking for a solution where you only use the primary file group would not be a supported solution.
I don't think there is a solution only using the primary file group unless the objects you're cleaning up are only on the primary file group.
When expressed that way, yes...I would agree with you :-). I guess I was looking at it backwards in the sense that I had this situation that wasn't factor for all this time (simply leaving the invalid object and defunct files), but now that it was (removing obsolete / invalid objects and files) I didn't have a solution that would work...and since the business unit didn't want to part with this particular database in a lower environment I was focusing primarily on a clean way to do what they were asking.
On a normal sized database I wouldn't have tried so long on fitting the square peg through the round hole. It was due to the size of the production system that I didn't have an area to easily do this and after not finding a working solution on what I had I went the next best route and used the previously unused production copy that was out of date / sync to perform the clean up on the massive amounts of filestream data.
Was a good practice none the less.
Thanks for all the replies.
Lee
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply