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
ON dbo.YourTableName(YourPKFields)

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
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?

  • I assume you mean "varbinary(max)" rather then "varbinary(1-8000)"? Same for varchar(max) not varchar(n). Note that text and image are deprecated – gbn Apr 16 '12 at 6:55
  • Yes, I mean that columns, I used text and image since they are better known. – Atilla Ozgur Apr 16 '12 at 7:03
  • @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. – DamagedGoods Feb 18 '14 at 11:29

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


| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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