曲演杂坛--收缩数据库数据文件

--=====================================================================

部分朋友在遇到收缩数据库文件的时候遇到一些困难，发现明明有大量剩余空间或删除了大量数据，还是无法收缩数据库，这是为啥子呢？

--====================================================================

要收缩数据库文件，首先我们需要确定有多少空间可以收缩,由于收缩文件是按照Extent来收缩，因此我们需要查找有多少未分配的分区。

--========================================================
--查看当前数据库下各文件的占用空间和可收缩空间
--FileSize_MB：当前文件的占用空间
--shrink_space_MB：可以收缩的最大空间（未使用的区所占空间）

SELECT DB_NAME() AS DatabaseName
,F.name
,F.physical_name
,F.size*8.0/1024 AS FileSize_MB
,U.total_page_count
,U.allocated_extent_page_count
,U.allocated_extent_page_count/8 AS allocated_extent_count
,U.unallocated_extent_page_count
,U.unallocated_extent_page_count/8 AS unallocated_extent_count
,U.unallocated_extent_page_count*8/1024 AS shrink_space_MB
FROM sys.database_files F
INNER JOIN sys.dm_db_file_space_usage U
ON F.file_id=U.file_id

或者使用

--==========================================
---显示指定的表或视图的数据和索引的碎片信息。
--使用(TotalExtents-UsedExtents)*8*8/1024 得到可以收缩的空间（单位MB）
DBCC SHOWFILESTATS

--===========================================================

在一些情况下，删除了大量数据，但是可以收缩的空间还是很小，这是为什么呢？让我们一步步来分析查找。

首先我们查看数据库上Extent的使用情况

--===========================================
--查看数据库TetDB1的各分区使用情况
CREATE TABLE #TmpExtentInfo
(
    [file_id] INT,
    [page_id] BIGINT,
    [pg_alloc] INT,
    [ext_size] INT,
    [object_id] BIGINT,
    [index_id] INT,
    [partition_number] INT,
    [partition_id] BIGINT,
    [iam_chain_type] VARCHAR(200),
    [pfs_bytes] VARCHAR(200)
)
GO
INSERT INTO #TmpExtentInfo
EXEC('DBCC EXTENTINFO(''TestDB1'')')

有了所有Extent的使用情况，我们便可以查找在已分配的数据区上未使用的空间情况

--==========================================================
--查找在已分配的Extent上未使用的数据页
SELECT OBJECT_NAME(object_id) AS TableName
,index_id
,SUM(ext_size- pg_alloc)*8.0/1024 AS UnusedSpaceInAllocatedExtent_MB
FROM #TmpExtentInfo
GROUP BY object_id,index_id
ORDER BY UnusedSpaceInAllocatedExtent_MB DESC

对于上面有大量未使用空间的用户对象，可以重建索引来释放分区。

--============================================================================================

如果未找到，我们就需要更深一步来数据页的使用情况

--===================================
--创建临时表来存放索引碎片信息
CREATE TABLE #TmpPageFrag (
   ObjectName char(255),
   ObjectId int,
   IndexName char(255),
   IndexId int,
   Lvl int,
   CountPages int,
   CountRows int,
   MinRecSize int,
   MaxRecSize int,
   AvgRecSize int,
   ForRecCount int,
   Extents int,
   ExtentSwitches int,
   AvgFreeBytes int,
   AvgPageDensity int,
   ScanDensity decimal,
   BestCount int,
   ActualCount int,
   LogicalFrag decimal,
   ExtentFrag decimal);

--===================================
--使用DBCC SHOWCONTIG来获取索引碎片信息
INSERT INTO #TmpPageFrag
EXEC('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;')

--==================================
--查找碎片较大的索引
SELECT * FROM #TmpPageFrag
ORDER BY CountPages*(100-AvgPageDensity)/100 DESC

当然我们也可以使用以下脚本来查找

SELECT OBJECT_NAME(object_id) AS TableName,*
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED')
ORDER BY page_count*(100-avg_fragmentation_in_percent) DESC

找到碎片较多的索引，可以通过索引维护来释放更多数据页

--===========================================================================================

因为收缩数据库文件是将已分配的页从数据文件的末尾移动到该文件前面的未分配页，因此我们需要先定位哪些“已分配的页”处于数据文件的末尾，由于数据页在文件中的偏移量=页ID*8192，因此页ID越大的数据页，处于文件的越尾端,因此我们需要查找页ID最大的对象

--===========================================
--查看数据库TetDB1的各分区使用情况
CREATE TABLE #TmpExtentInfo
(
    [file_id] INT,
    [page_id] BIGINT,
    [pg_alloc] INT,
    [ext_size] INT,
    [object_id] BIGINT,
    [index_id] INT,
    [partition_number] INT,
    [partition_id] BIGINT,
    [iam_chain_type] VARCHAR(200),
    [pfs_bytes] VARCHAR(200)
)
GO
INSERT INTO #TmpExtentInfo
EXEC('DBCC EXTENTINFO(''TestDB1'')')
--==========================================================
--查找在数据文件尾端的对象
SELECT OBJECT_NAME(object_id) AS TableName
,*
FROM #TmpExtentInfo
ORDER BY page_id DESC