曲演雜壇--收縮數據庫數據文件

2024-07-21 02:50:01

字體：大中小

來源：轉載

供稿：網友

曲演雜壇--收縮數據庫數據文件

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

部分朋友在遇到收縮數據庫文件的時候遇到一些困難，發現明明有大量剩余空間或刪除了大量數據，還是無法收縮數據庫，這是為啥子呢？

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

要收縮數據庫文件，首先我們需要確定有多少空間可以收縮,由于收縮文件是按照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_MBFROM sys.database_files FINNER JOIN sys.dm_db_file_space_usage UON 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))GOINSERT INTO #TmpExtentInfoEXEC('DBCC EXTENTINFO(''TestDB1'')')

有了所有Extent的使用情況，我們便可以查找在已分配的數據區上未使用的空間情況

--==========================================================--查找在已分配的Extent上未使用的數據頁SELECT OBJECT_NAME(object_id) AS TableName,index_id,SUM(ext_size- pg_alloc)*8.0/1024 AS UnusedSpaceInAllocatedExtent_MBFROM #TmpExtentInfoGROUP BY object_id,index_idORDER 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 #TmpPageFragEXEC('DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES;')--==================================--查找碎片較大的索引SELECT * FROM #TmpPageFragORDER 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))GOINSERT INTO #TmpExtentInfoEXEC('DBCC EXTENTINFO(''TestDB1'')')--==========================================================--查找在數據文件尾端的對象SELECT OBJECT_NAME(object_id) AS TableName,*FROM #TmpExtentInfoORDER BY page_id DESC