Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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 at 11:29
add comment

1 Answer 1

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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