What is Proper Steps to Remove FileGroups

Question

What is Proper Steps to Remove FileGroups

stanlyn

Say Hey Kid

Points: 701
More actions
September 13, 2012 at 11:52 am

#281533

Hi,
I have some filegroups that contains tables with data in them that I need to drop and remove all evidence of them. I have tried various combinations and what ultimately happens I end up with orphans and they won't go away.
I've tried in this order:
1. taking a truncate log backup,
2. dropping the tables, (this works)
3. removing the .ndf and .ldf files, (this shows to work, but revisiting later shows the .ldf file, while the .ndf files are removed as expected)
4. removing the filegroup, (works)
Does sql server put this on the back burner to process? The table in the filegroup is about 60gb in size. Would that be why the .ldf file is still hanging around?
Is there a way for sql server to quit wasting my time by quickly blast them away?
What is the best way to do this?
Thanks, Stanley
PS: Sorry about my impatience, but I come from a Visual FoxPro environment where things happen immediately, but I guess that's evolution...

Viewing 15 posts - 1 through 15 (of 38 total)

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

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

.ldf files are not part of filegroups. That's the transaction log.

To drop a filegroup, you drop all tables and indexes in that filegroup, then drop the ndf files in there, then drop the filegroup. If you've successfully done all that (as you indicated) then you have dropped the filegroup completely and there's nothing to wait for.

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

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

Hi Gail,

Each filegroup contains a .ndf and a .ldf file and its the .ldf file thats still hanging around. I know you said that .ldf is a log file, but in this case the associated .ldf file to this filegroup is there. It can't be removed from the files section.

How then can this .ldf file be removed as its the only file left after removing the other filegroup components. I do remember creating these particular log files when creating the filegroups. Should they have been created at all?

Thanks, Stanley

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

So, does this mean that I have multiple .ldf files that all belong to the main database and NOT to the filegroup that I was thinking they were to belong to?

Thanks, Stanley

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

ldf files never belong to a filegroup. They're the transaction log. Only data files can be part of a filegroup.

If you check what files comprise a filegroup, you'd see the ldf did not.

Now, you probably only want one ldf file, but removing the other ones isn't quite as trivial as dropping a filegroup.

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

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

Hi Gail,

After individually deleting the indexes and tables that makes up the filegroup they are now removed as this screenshot indicates. The table names were ImagesPDF, ImagesSLA and ImagesTIF...

This is what the filegroups section looks like...

I get this error message when clicking the OK button after removing the files and filegroups. They complain the tables are not empty. That is true, however I've already deleted them, so why does it matter?

and

So how do I recover from this as the tables were successfully deleted and now I picking up orphans? What do I do next?

Thanks, Stanley

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

Hi Gail,

Would this work?

1. Enter single user maintenance mode

2. Do a files and filegroup FULL backup excluding the unwanted filegroups,

3. detach the database,

4. attach the database with the extra filegroups and .ldf files removed/excluded.

5. run ??command?? that will recreate the .ldf file

6. run other ????commands????

What commands should I run and where in this stack should I run them?

Thanks, Stanley

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

No, no, no, no!!!!!! Do not ever delete ldf files. Will likely leave your DB damaged and unusable.

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

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

Don't see any screenshots.

You have to drop all tables and indexes that are on the filegroup you want to remove. Not empty the tables, drop them.

Once there are no objects at all in the filegroups, you should be able to drop the files and the filegroup.

As for the log, you can only drop a log file if there is no portion of the active log in that file.

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

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

Hi Gail,

OK, then how is the best way to get the unneeded ldf files removed while keeping only one?

What I outlined above came from an article I've read somewhere that suggests a new ldf would be created. It was important to get a full backup prior and at that point the log file could be replaced with a new auto generated one by restoring the database without the original ldf file in the restore.

So, what would you do?

Thanks again, Stanley

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

stanlyn

Say Hey Kid

Points: 701

September 13, 2012 at 1:42 pm

#1537449

Check for screenshots now...

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

Gail,

>> You have to drop all tables and indexes that are on the filegroup you want to remove. Not empty the tables, drop them. Once there are no objects at all in the filegroups, you should be able to drop the files and the filegroup.

Yes, this is how I done it and the screenshots shows the tables removed, but when trying to remove the files and groups, it complains that the removed tables are not empty.

The screenshots are showing up here just fine...

Thanks, Stanley

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

No, it's not complaining that the tables are not empty. It's complaining that the file is not empty. There's still an index or a table in that filegroup, you have to find all the objects in the filegroup and drop them before you can drop the filegroups.

What I outlined above came from an article I've read somewhere that suggests a new ldf would be created. It was important to get a full backup prior and at that point the log file could be replaced with a new auto generated one by restoring the database without the original ldf file in the restore

Put that article into the garbage, because that's all it is. SQL might create a new log file. Or it might not be able to and trash your DB. Also you can't restore a database without all its log files.

For dropping a log file, as I said, you can only drop log files that aren't in use. DBCC SQLPerf(LogSpace) to identify which log files are in use. If one is in use, you have to wait (not for time, for transactions to run) until that log file does not contain the active portion of the log, then you can drop the file.

Anyone there that knows SQL well?

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

Lowell SSC Guru Points: 323518 More actions · Answer 13

something like this might help you identify all the objects that are still in the filegroup(s) in question.

you would have to move the clstered index of any tables that exist in the group you wnat to remove.

SELECT

objz.[name],

objz.[type],

idxz.[name],

idxz.[index_id],

CASE idxz.[index_id]

WHEN 0 THEN 'HEAP'

WHEN 1 THEN 'CLUSTERED'

ELSE 'NON-CLUSTERED'

END AS index_type,

filz.[name]

FROM sys.indexes idxz

INNER JOIN sys.filegroups filz

ON idxz.data_space_id = filz.data_space_id

INNER JOIN sys.all_objects objz

ON idxz.[object_id] = objz.[object_id]

WHERE idxz.data_space_id = filz.data_space_id

AND objz.type_desc IN( 'USER_TABLE') -- User Tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

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

Hi Lowell,

Running your script returns no records and I see no way of identifying what else is there. I DID explicitly deleted all indexes first before deleting the table.

Each filegroup contained 1 table and 2 indexes, of which are now deleted.

Any other ideas?

Thanks, Stanley