What is Proper Steps to Remove FileGroups

Question

What is Proper Steps to Remove FileGroups

Viewing 9 posts - 31 through 38 (of 38 total)

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

stanlyn Say Hey Kid Points: 701 More actions · Answer 1

Gail,

>> Also, I suggest maybe not taking DBA advice from developers without double checking it (Stack overflow is a developer site)

That is what I'm doing here right now. I'm measuring twice before cutting...

Again, your point is well taken and respected!

I'm looking for a solution and the proper way to solve this and so far I found several articles similar to the one posted and comments discussing its complexity, and thanks for these comments as I learn from them.

Currently, I can't trash it as I have no other solutions on the table (or maybe I'm missing something here).

Bottom line is that if I can't find any other solutions to this issue, then I'll be forced to try whatever method offers some hope, right or wrong, and I'll have to chuck it up as Harvard tuition and learn from it.

Thanks, Stanley

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Run the query I posted earlier and post the results please.

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

ScottPletcher SSC Guru Points: 101089 More actions · Answer 3

You can use this to check for files that still exist in a given filegroup(s):

SELECT

FILEGROUP_NAME(a.data_space_id) AS File_Group,

OBJECT_NAME(p.object_id) AS Table_Name,

i.name AS Index_Name

FROM sys.allocation_units a

INNER JOIN sys.partitions p ON

a.container_id = CASE WHEN a.type in(1,3) THEN p.hobt_id ELSE p.partition_id END AND

p.object_id > 1024

LEFT JOIN sys.indexes i ON

i.object_id = p.object_id AND i.index_id = p.index_id

ORDER BY

File_Group, Table_Name, Index_Name

And I agree with everyone else on deleting log files -- DO NOT DELETE LOG FILES EVER!

EDIT: Sorry, didn't see SQLRNNR's earlier post with the same type of query.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

stanlyn Say Hey Kid Points: 701 More actions · Answer 4

Hi Jason,

Running your script returns:

The deedroom db should consist of 2 filegroups "primary" and "FG_Images". The script output shows that.

As stated elsewhere in this thread, the deedroom db has 3 other filegroups that do not want to be deleted. Their names are FG_ImagesSLA, FG_ImagesTIF, and FG_ImagesPDF. These files and fgs are showing up in the file and fg sections of ssms. Their .ldf files are also deletion resistant...

Whats next?

Thanks, Stanley

CapnHector SSCoach Points: 16143 More actions · Answer 5

SQLRNNR (9/13/2012)
stanlyn (9/13/2012)
Gail,
From that article, did you read the very last part as show here:
^{It is quite simple to fix this error, but you will need to delete all the Metadata pertaining to the DB from system files. The easiest way to do that would be to drop the DB. Here is what you will need to do.}
Take the DB offline or detach the DB
Make a copy of the .MDF file to your backup directory
Now Drop the DB (You will have to reattach the DB, if you detached, of course)
Copy the .MDF back to the original location
After these steps, run the following:
SP_ATTACH_SINGLE_FILE_DB @dbname='<<DBName>>' ,@physname=N'<<filepath\filename.MDF>>'
This should return you the following:
File activation failure. The physical file name "<<Filepath\filename.ldf>>"
may be incorrect.
New log file '<<Filepath\filename.ldf>>' was created.
That would bring your DB back to an usable state.
So should this be trashed?
Thanks, Stanley
This is not guaranteed to work. I have seen it fail plenty. It is best to avoid dropping the log files if there is no need to do it (leaving no log files available) or dropping any log file that has active transactions in it.

i had an issue where i thought about trying this technique and im damn glad i took a backup before i did and tried this on a dev server. it failed in a flaming ball of fire. if i had not taken the backup i would have been screwed. we were moving the DB to a new server and were fixing file sizes (previous developer was a little lazy). figured id give it a shot as it was the fastest way (the fastest way to get to the bottom of a tall building is to jump). allot of times the fastest way is not the best.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

stanlyn Say Hey Kid Points: 701 More actions · Answer 6

Hi Scott,

Running your script, it returns:

Which begs the question on why the Page table has 3 PK_Page_Identity indexes, as it should have only one. That's the only thing I see that warrants further investigation after looking at all 126 rows.

What else are we looking for?

Thanks, Stanley

ScottPletcher SSC Guru Points: 101089 More actions · Answer 7

stanlyn (9/13/2012)
Hi Scott,
Running your script, it returns:
Which begs the question on why the Page table has 3 PK_Page_Identity indexes, as it should have only one. That's the only thing I see that warrants further investigation after looking at all 126 rows.
What else are we looking for?
Thanks, Stanley

Probably just different allocation chunks -- sorry, I should probably put a DISTINCT in there, just haven't gotten around to it yet :-).

We're really just looking for objects lingering in the filegroups you want to remove.

If the db isn't too large, you might also want to insure everything in the system tables is up-to-date via:

USE DeedRoom

DBCC UPDATEUSAGE ( 0 )

DBCC CHECKDB ( 0 ) WITH NO_INFOMSGS

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

Run the following:

SELECT name,physical_name

FROM sys.master_files

WHERE DB_NAME(database_id) = 'deedroom'

I want to see what the catalog views have to say about this issue on the instance you are seeing this issue.

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

stanlyn Say Hey Kid Points: 701 More actions · Answer 9

Hi Jason,

I was able to resolve the issues without deleting any of the main log files. I still do not have a step by step on how to do this. I resolved it by contiguously doing log backups and filegroup shrinks on the filegroups and logs that was to be deleted. To avoid fragmentation, shrink was not applied anywhere else.

Thanks for your interest and help,

Stanley