5

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. Do other approaches exists?

1
  • @Atilla watch out for the script generated by SSMS. As you know it will drop and create your table. The way it is generated if you stop the script it will not rollback gracefully, leaving you with missing constraints or an open transaction (Have encountered both several times as had to do complete this on a massive DB with no valid testing due to concurrency and data size). One other tip is that I had several huge tables with 'note' data that I was not bothered if I lost a record or 2. I removed the TABLOCK from the script and copied data, or did it in chunks if a timestamp available. Feb 18, 2014 at 11:29

2 Answers 2

9

Kimberly Tripp & Paul Randal has a couple of excellent posts on this very subject:

However, there is an interesting trick, one that does NOT require dropping/recreating keys but will still be an OFFLINE operation in versions prior to SQL Server 2012. It’s to create the table on a partition scheme using the DROP_EXISTING option. This moves the data and the LOB data to the SAME filegroup (you do NOT have an option to separate them) but you can still move them and with little work. What will be required are a partition function and a partition scheme. The data does not have to be physically partitioned but the data will only move when the table/clustered index goes from partitioned to non-partitioned or vice versa OR from one partition scheme to another.

As an alternative strategy (and this might be difficult depending on how many other objects reside on the same filegroup), you can change the location of the files in the filegroup on which your table resides. >... Basically, it consists of adding a new file to existing filegroup which is the total size of all of the existing files. Once this new file is added, ALL of the other files are emptied into this new file by using the EMPTYFILE parameter of DBCC SHRINKFILE. When the file is emptied, all allocations are moved to the file with the most free space. So, you must make the newly added file the total size of the filegroup. Once everything has been emptied, you can remove these files from the database using ALTER DATABASE… REMOVE FILE.

0
2

The sp_MoveTablesToFileGroup procedure by Mark White (available for example at https://github.com/YPermitin/SQLServerTools/blob/master/SQL-Server-File-Groups/Move-Data-Between-Filegroups/sp_MoveTablesToFileGroup.sql) automates the move between filegroups (you can move it directly or generate a script to move the tables).

It uses the same idea as the marked answer (creating a partition function), but instead of having to find out the biggest / smallest ID for partitioning it just places both partitions into the target filegroup.

CREATE PARTITION FUNCTION PF_MOVE_HELPER_B0DE29720A214194B51D0D190927A58D ([bigint] ) AS RANGE RIGHT FOR VALUES (0);
CREATE PARTITION SCHEME PS_MOVE_HELPER_B0DE29720A214194B51D0D190927A58D AS PARTITION PF_MOVE_HELPER_B0DE29720A214194B51D0D190927A58D TO ([TARGET],[TARGET]);

And then it re-creates the index once with partitioning, and once without.

1
  • It is even better to create a partition function will an empty VALUES list.
    – Paul White
    Mar 9 at 12:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.