首頁>Database>source

是否可以執行查詢以查看檔案組中给定檔案的最大磁盤空間消耗表?

需要為给定的SAN驅動器釋放一些磁盤空間,並且需要確定哪些表正在使用该驅動器上的几个資料檔案的最大空間。

最新回復
  • 7月前
    1 #

    您可以轉到報告 ->標準報告,然後選擇"按頂層表使用磁盤".這將给您表名的降序使用,从磁盤上的最大值到磁盤上的最小值。

    或者,您可以使用下面的SQL来获取详细資訊:

    SELECT sys.objects.name, SUM(reserved_page_count) * 8.0 / 1024 as Size
    FROM sys.dm_db_partition_stats, sys.objects 
    WHERE sys.dm_db_partition_stats.object_id = sys.objects.object_id 
    GROUP BY sys.objects.name
    ORDER BY Size DESC;
    GO
    

    或此查詢:

    SELECT TOP 1000
            a3.name AS SchemaName,
            a2.name AS TableName,
            a1.rows as Row_Count,
            (a1.reserved )* 8.0 / 1024 AS reserved_mb,
            a1.data * 8.0 / 1024 AS data_mb,
            (CASE WHEN (a1.used ) > a1.data THEN (a1.used ) - a1.data ELSE 0 END) * 8.0 / 1024 AS index_size_mb,
            (CASE WHEN (a1.reserved ) > a1.used THEN (a1.reserved ) - a1.used ELSE 0 END) * 8.0 / 1024 AS unused_mb
        FROM    (   SELECT
                    ps.object_id,
                    SUM ( CASE WHEN (ps.index_id < 2) THEN row_count    ELSE 0 END ) AS [rows],
                    SUM (ps.reserved_page_count) AS reserved,
                    SUM (CASE   WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
                                ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count) END
                        ) AS data,
                    SUM (ps.used_page_count) AS used
                    FROM sys.dm_db_partition_stats ps
                    GROUP BY ps.object_id
                ) AS a1
        INNER JOIN sys.all_objects a2  ON ( a1.object_id = a2.object_id )
        INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
        WHERE a2.type <> N'S' and a2.type <> N'IT'   
        order by a1.data desc        
    

    註意:我自己尚未測試這些查詢,因此請確认是否有效。

    這个問题已经在多个論坛上被問到,我在這裏使用了答案。

    就檔案而言,您可能需要列出檔案組,如果未定義檔案組,則所有分配都將在主檔案組中进行。

    以下查詢可以帮助您找到實際位置:

    SELECT  OBJECT_NAME(i.id)   AS [Table_Name]
           , i.indid
           , i.[name]           AS [Index_Name]
           , i.groupid
           , f.name             AS [File_Group]
           , d.physical_name    AS [File_Name]
           , s.name             AS [Data_Space]
    FROM        sys.sysindexes i
    INNER JOIN  sys.filegroups f        ON  f.data_space_id = i.groupid 
                                        AND f.data_space_id = i.groupid
    INNER JOIN  sys.database_files d    ON  f.data_space_id = d.data_space_id
    INNER JOIN  sys.data_spaces s       ON  f.data_space_id = s.data_space_id
    WHERE       OBJECTPROPERTY(i.id, 'IsUserTable') = 1
    ORDER BY    f.name, OBJECT_NAME(i.id), groupid
    

    希望這会有所帮助。

  • sql server:使用功能更新時的遠端掃描
  • windows:無法重設MySQL密碼