December 23, 2009 at 2:32 pm
I'm moving some tables from a secondary filegroup (.ndf) back into the primary filegroup (.mdf). I'm doing this by changing the filegroup that the clustered index is on for each table, and if necessary any other indexes.
When I finished, I tried to remove the file with the command:ALTER DATABASE <db> REMOVE FILE [<logical file name>}Running this produces this error:
Msg 5042, Level 16, State 1, Line 1
The file 'rotc_ref_Data' cannot be removed because it is not empty.
After searching the internet for a while, I ran this code to see what all is still in the filegroup:
SELECT a.*, Object_Name(p.object_id), p.*
FROM sys.allocation_units a
INNER JOIN sys.filegroups fg
ON fg.data_space_id = a.data_space_id
AND fg.name = 'ROTC_REF_DATA'
LEFT OUTER JOIN sys.partitions p
ON p.partition_id = a.container_id
AND a.type = 2
This returns three records, all of type 2 (type desc="LOB_DATA"). In looking at the tables (object_name(object_id)), I see that all three tables have either a text, ntext or image column. I'm assuming that this is the LOB_DATA being referred to.
So, what do I need to do to move this LOB_DATA back into the Primary filegroup?
Thanks,
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 23, 2009 at 2:45 pm
I am not aware of a way to do this other than creating a new table having the same structure as the old one. Specify the desired file groups you want to use for the text and image columns. Then you insert all rows from the old table into the new table. Drop the old table and rename the new one.
Actually, if you do this you should be using varchar(MAX) and varbinary(MAX) instead of text and image because they are deprecated, so create the columns as these new types.
The probability of survival is inversely proportional to the angle of arrival.
December 23, 2009 at 5:34 pm
You can do this using an ALTER TABLE command and specifying a different filegroup for the BLOB data. ALTER TABLE can also be used to move data between filegroups where you don't have a clustered index.
December 24, 2009 at 9:33 am
sturner (12/23/2009)
I am not aware of a way to do this other than creating a new table having the same structure as the old one. Specify the desired file groups you want to use for the text and image columns. Then you insert all rows from the old table into the new table. Drop the old table and rename the new one.Actually, if you do this you should be using varchar(MAX) and varbinary(MAX) instead of text and image because they are deprecated, so create the columns as these new types.
This worked, though I would have preferred a method that just transferred it over without having to use an intermediate table. Thanks for your help!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 24, 2009 at 9:39 am
Glenn Dorling (12/23/2009)
You can do this using an ALTER TABLE command and specifying a different filegroup for the BLOB data. ALTER TABLE can also be used to move data between filegroups where you don't have a clustered index.
I couldn't find in BOL where you could specify a filegroup for the BLOB data with the ALTER TABLE command. It also looks like you can only transfer the data to a different filegroup concurrent with dropping a constraint.
Thanks for your help!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply