IT工程師數位筆記本

數據庫管理員有時候需要控制文件的大小，可能選擇收縮文件，或者把某些數據文件情況以便從數據庫里刪除。

這時候我們就要使用到DBCC SHRINKFILE命令，此命令的腳本為：

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)
[ WITH NO_INFOMSGS ]

由于DBCC SHRINKFILE一次運行會同時影響所有的文件（包括數據文件和日志文件），使用者不能指定每個文件的目標大小，其結果可能不能達到預期的要求。建議是做好規劃，對每個文件確定預期目標，然后使用DBCC SHRINKFILE來一個文件一個文件的做比較妥當。

要注意一些幾點：

1、首先要了解數據文件當前使用的使用情況。

收縮量的大小不可能超過當前文件的空閑空間的大小。如果想要壓縮數據庫的大小，首先就要確認數據文件的確有相應未被使用的空間。如果空間都在使用中，那就要先確認大量占用空間的對象，比如：表格或索引，然后通過歸檔歷史數據，先把空間釋放出來。

2、主數據文件（primary File)是不能被清空的。能被完全清空的是有輔助數據文件。

3、如果要把一個文件組整個清空，要刪除分配在這個文件組上的對象（表格或索引），或者把它們移動到其它文件組上，DBCC SHRINKFILE不會幫你做這個工作。

把數據文件里面該刪除的數據和對象清除完、確認數據文件（組）有足夠的空閑空間后，管理員就可以下DBCC SHRINKFILE命令來縮小或清空指定文件了。如果是要縮小文件，就填寫上要的tearget_size,如果要清空文件，就選擇EmptyFile。SQL Server在做DBCC ShrinkFile的時候，會掃描數據文件并對正在讀的頁面加鎖。所以對數據庫的性能會有所影響。但是這不是一個獨占的行為，也就是說在收縮的時候，其他用戶照樣可以對數據庫進行讀寫訪問。所以不需要單獨安排服務器停機時間來做，一般在數據庫維護的時候就可以進行。可以在進程中的任意點停止DBCC SHRINKFILE操作，任何已完成的工作都會保留。如果操作沒有在規定的時間內完成，也可以完全的停止它。

可是，有時候明明看到數據文件里有空間，為什么就是不能壓縮或者情況它呢？這通常是因為數據文件里面雖然有很多空的頁面，但是這些頁面分散在各個區里，使得整個文件沒有很多空的區。

需要說明的是，DBCC SHRINKFILE做的，都是區一級的動作。它會把使用過的區前移，把沒有使用中的區從文件中移除。但是，它不會把一個區里面的空頁面移除、合并區，也不會在頁面里面的空間移除、合并頁面。所以，一個數據庫中有很多只使用了一兩個頁面的區，DBCC SHRINKFILE的效果會不明顯。

下面的案例來展示這個過程：

我們新建一個每一行都會占用一個頁面的表格。表上沒有聚集索引，所以是一個堆表。往里面插入8000條數據：

create table show_extent
(
   a int,
   b nvarchar(3900)
)
go
declare @i int
set @i=1
while @i<=1000
begin
    insert into show_extent values(1,replicate(N'a',3900))
    insert into show_extent values(2,replicate(N'b',3900))
    insert into show_extent values(3,replicate(N'c',3900))
    insert into show_extent values(4,replicate(N'd',3900))
    insert into show_extent values(5,replicate(N'e',3900))
    insert into show_extent values(6,replicate(N'f',3900))
    insert into show_extent values(7,replicate(N'g',3900))
    insert into show_extent values(8,replicate(N'h',3900))
    set @i=@i+1
end

dbcc showcontig('show_extent')
go 

可以看到這個表有1003個區，然后平均每個區里面有8個頁面，共計8000個頁面...當然這里面包含區碎片所以多處了3個區，24個頁的冗余

我們下面刪除一部分數據，只保留a=5的這些個記錄，來對比前后的空間大小

sp_spaceused show_extent
go
delete show_extent where a<>5
go
sp_spaceused show_extent
go
dbcc showcontig('show_extent')
go   

可以看到...刪除之后的頁面空間是沒有釋放的。只是縮小了一點點。

區沒有變化，頁數減少了才一半左右，也就是說每個區平均現在只有4.1個頁面，在這種情況下去收縮數據庫是沒有效果的。

我們下面收縮一些看看效果

DBCC SHRINKFILE(1,40)

可以看到頁數不但沒有減少，而且增加了..這地方的原因是它按照邏輯分區計算的：1002*8約等于8016..

可以看到能夠收縮的空間很少很少..而且給出了一個好的解決方法就是：重新組織頁，但是這樣會影響性能，也就是說這樣它會重新組織頁，填充頁。

當然我們這里還有另外一個解決方案，通過重建索引的方式把頁面重新排列一次。現在還沒有聚集索引，我們給他新建一個

create  clustered  index show_I
on show_extent(a)
go
dbcc showcontig('show_extent')

可以看到...立馬縮減到1000頁，125個區，我們來看看能收縮多少數據。

 現在可以看到了可以收縮的百分比了。我們來執行收縮數據操作

可以看到我們已經將數據收縮至5120，說明此時收縮數據已經產生了作用。

如果不想建立聚集索引，可以把這張表的數據先移走，然后清空表格，再把數據插回來。當然這樣比較麻煩，還是有聚集索引管理起來比較方便。

剛才談到了造成Shrinkfile效果不佳的情況。在一個有聚集索引的的表格上，可以通過重建建立聚集索引來解決。但是如果區里面存放的是text或者image之類的數據類型，SQL Server會單獨的頁面來存放這些數據。如果這類存儲頁面也發生同樣的問題，和堆一樣，做索引重建也不會影響到他們。

對于這種對象的處理方式，就是把這些可能有問題的對象都找出來，然后重新建立他們。可以利用DBCC Extentinfo這個命令打出數據文件里面的所有區的分配信息。然后計算每個對象理論上區的數目和實際數目。如果實際數目遠大于理論的數目。那這個對象就是存在多于的碎片，需我們考慮重建對象了。

我們下面來看一個例子： 

if exists(select name from sysobjects where NAME ='extentinfo' and type='U')

drop table extentinfo

go

create table extentinfo 

( [file_id] smallint,

page_id int,

pg_alloc int,

ext_size int,

obj_id int,

index_id int,

partition_number int,

partition_id bigint,

iam_chain_type varchar(50),

pfs_bytes varbinary(10) )

go

if exists(select name from sysobjects where NAME ='import_extentinfo' and type='P')

drop procedure import_extentinfo

go

create procedure import_extentinfo 

as dbcc extentinfo('TestDB')

go

insert extentinfo 

exec import_extentinfo

go 

select name as table_name,

[file_id],obj_id, index_id, partition_id, ext_size,

'actual page count'=sum(pg_alloc),

'actual extent count'=count(*),

'expected extent count'=ceiling(sum(pg_alloc)*1.0/ext_size), --一個對象的所有盤區頁數的各總和/

'expected extents / actual extents' = (ceiling(sum(pg_alloc)*1.00/ext_size)*100.00) / count(*)

from extentinfo inner join sysobjects

on obj_id=id

group by [file_id],obj_id, index_id,partition_id, ext_size ,name

having count(*)-ceiling(sum(pg_alloc)*1.0/ext_size) > 0 

order by partition_id, obj_id, index_id, [file_id]

這里我們通過DBCC extentinfo命令來查看數據庫中的區明細，然后查看理論值和實際值的差距，如果存在大量的碎片，我們就需要進行重建清理了。

DBCC EXTENTINFO命令用于查詢某個數據庫、或者某個數據對象（主要是數據表）的盤區分配情況。其語法結構如下：
DBCC
EXTENTINFO（dbname,tablename,indexid）

我們給出這個命令顯示行的明細：

我們來找個數據庫看一下存在碎片的情況：

可以看到上圖中，箭頭所指的這個行數據，理論應該為一個區就可以，但是實際上它建立了兩個分區...所以這種情況可以考慮整理碎片，進行重建，釋放碎片。