如何在SQL Server中查找文件组内容的类型和大小? [英] How to find type and size of content of a filegroup in SQL server?

问题描述

我有一个使用两个文件组的数据库.我们称它们为PRIMARY和FG1.所有数据最初都位于PRIMARY中，然后移至FG1中.这是通过将聚簇索引移至FG1来实现的.其他索引在未指定文件组的情况下被删除并重新创建，但是FG1被定义为默认索引，因此它们现在在FG1中有效.

I have a database using two filegroups. Let's call them PRIMARY and FG1. All data was originally in PRIMARY and then moved to be in FG1. This was achieved by moving the clustered indexes to FG1. The other indexes were removed and recreated without specifying a file group, but FG1 was defined as default so they are now effectively in FG1.

但是，PRIMARY的文件仍然会随着时间的推移而被填充.

However, the file for PRIMARY still gets filled over time.

我如何找到保留在PRIMARY中的东西的类型，包括它的大小?
我的目标是将所有事情都交给FG1，这样PRIMARY就不会再出现了.

How can I find the type of stuff that remains in PRIMARY, including its size?
My goal is to get really everything over to FG1 so that PRIMARY doesn't get filled anymore.

推荐答案

下面是一个脚本，其中列出了所有文件组中的所有对象和所有索引:
http://gallery.technet.microsoft.com/scriptcenter/c7483555 -cc22-4f6c-b9c4-90811eb3bdb6

Here's a script which lists all objects and all indexes in all filegroups:
http://gallery.technet.microsoft.com/scriptcenter/c7483555-cc22-4f6c-b9c4-90811eb3bdb6

-- List all Objects and Indexes 
-- per Filegroup / Partition and Allocation Type 
-- including the allocated data size 
SELECT DS.name AS DataSpaceName 
  ,AU.type_desc AS AllocationDesc 
  ,AU.total_pages / 128 AS TotalSizeMB 
  ,AU.used_pages / 128 AS UsedSizeMB 
  ,AU.data_pages / 128 AS DataSizeMB 
  ,SCH.name AS SchemaName 
  ,OBJ.type_desc AS ObjectType       
  ,OBJ.name AS ObjectName 
  ,IDX.type_desc AS IndexType 
  ,IDX.name AS IndexName 
FROM sys.data_spaces AS DS 
 INNER JOIN sys.allocation_units AS AU 
     ON DS.data_space_id = AU.data_space_id 
 INNER JOIN sys.partitions AS PA 
     ON (AU.type IN (1, 3)  
         AND AU.container_id = PA.hobt_id) 
        OR 
        (AU.type = 2 
         AND AU.container_id = PA.partition_id) 
 INNER JOIN sys.objects AS OBJ 
     ON PA.object_id = OBJ.object_id 
 INNER JOIN sys.schemas AS SCH 
     ON OBJ.schema_id = SCH.schema_id 
 LEFT JOIN sys.indexes AS IDX 
     ON PA.object_id = IDX.object_id 
        AND PA.index_id = IDX.index_id 
ORDER BY DS.name 
    ,SCH.name 
    ,OBJ.name 
    ,IDX.name

感谢@RaphaëlAlthaus提供有关问题评论的链接.

Thanks @Raphaël Althaus for the link in the comments on the question.

关于我的问题的第二点，我无法从PRIMARY中移动剩余的对象，因为它是LOB数据.根据创建表的文档，存储任何大列随后不能更改在CREATE TABLE中指定的数据."运气不好.

Regarding the second point in my question I couldn't move the remaining objects from PRIMARY as it is LOB data. According to the documentation of CREATE TABLE, "the storage of any large column data specified in CREATE TABLE cannot be subsequently altered." Bad luck.

这篇关于如何在SQL Server中查找文件组内容的类型和大小?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！