由于前期设计表时,开发人员没有按照文件组的类别存储新表,导致表建在默认文件组primary下,通过新建索引的方法 可以将表迁移到目标文件组,但是lob_data类型数据无法移动,会继续待在primary文件组,目前的解决办法就是新建表,然后 insert into newtable as select * from oldtable,再删除原来表,最后重命名。效果可以。
但是如果想知道primary文件组里还有哪些类似这种lob_data 数据遗留的表,可以用以下代码:
--查询'PRIMARY'文件组内含有哪些表select sb.xtype,count(sb.xtype)
from
sys.partitions pt
join sys.allocation_units aut on
(type in(1,3) and aut.container_id = pt.hobt_id or
type in(2) and aut.container_id = pt.partition_id)
join sys.filegroups fg on fg.data_space_id=aut.data_space_id
join sysobjects sb on pt.object_id = sb.id
--where pt.object_id=object_id('tablename')
where fg.name = 'PRIMARY'
and aut.type_desc ='LOB_DATA'
group by sb.xtype ;
IT 3 内部表
S 8 系统表
U 45 用户表
--'PRIMARY'文件组里面用户表LOB_DATA数据类型占有量
select sum(aut.used_pages)
from
sys.partitions pt
join sys.allocation_units aut on
(type in(1,3) and aut.container_id = pt.hobt_id or
type in(2) and aut.container_id = pt.partition_id)
join sys.filegroups fg on fg.data_space_id=aut.data_space_id
join sysobjects sb on pt.object_id = sb.id
--where pt.object_id=object_id('tablename')
where fg.name = 'PRIMARY'
and aut.type_desc ='LOB_DATA'
and sb.xtype = 'U';--用户表
--526458页
哪里有不对的欢迎提出交流。