LOB_DATA : varbinary(max),varchar(max), image , text columns.
I have 4 tables with large amount of LOB_DATA in it. I would like to move LOB_DATA in these tables to different File group in a different hard disk.
I have created a new filegroup and file in this filegroup. I also used sql command from this question. But varbinary column storage remains where it was before.
-- To move table data to new filegroup
CREATE UNIQUE CLUSTERED INDEX PK_YourTableName
ON dbo.YourTableName(YourPKFields)
WITH (DROP_EXISTING = ON) ON [NewFilegroup]
I use following sql to see filegroup of lob_data.
-- To see lob_data filegroups
SELECT OBJECT_NAME(object_id) as OBJECT_NAME, FILEGROUP_NAME(data_space_id) as
FILE_GROUP_NAME, type_desc
FROM sys.partitions p
JOIN sys.allocation_units a
on p.partition_id = a.container_id
WHERE
type_desc = 'LOB_DATA'
I am using SQL Server 2008.
I have found following solution how-to-move-the-lob-data-from-one-file-group-to-other. Is there exists another approach?
