sqlserver收縮資料庫、收縮資料檔案的操作

一些實際工作中的總結

1、實際工作中收縮資料檔案的情況比收縮庫的情況多，不建議直接收縮資料庫

2、收縮很容易出現等待，收縮會話對應sys.sysprocesses的欄位waitresource值類似為15:1:4700649，sys.sysprocesses的欄位lastwaittype值為PAGEIOLATCH_SH或PAGEIOLATCH_EX等

3、收縮資料檔案時，不要一次性全部收縮。 可以每次收縮5G左右，比如DataFile1有32G，則每次收縮如下

USE UserDB;

DBCC SHRINKFILE (DataFile1, 27000);

GO

DBCC SHRINKFILE (DataFile1, 22000);

GO

4、資料檔案的可用空間可以結合sys.master_files和FILEPROPERTY(name,'SpaceUsed')來檢視

5、收縮的100%進度可以透過sys.dm_exec_requests的欄位percent_complete來看

6、收縮完後，記得重建索引

alter index all on table_name rebuild with (>

收縮資料庫的官方文件

DBCC SHRINKDATABASE

( database_name | database_id | 0

[ , target_percent ]

[ , { NOTRUNCATE | TRUNCATEONLY } ]

)

[ WITH NO_INFOMSGS ]

database_name | database_id | 0

要收縮的資料庫名稱或 ID。 0 指定使用當前資料庫。

target_percent

整數，資料庫收縮後的資料庫檔案中所需的剩餘可用空間百分比。

NOTRUNCATE

將分配的頁面從檔案的末尾移動到檔案前面的未分配頁面。 此操作會壓縮檔案中的資料。

檔案末尾的可用空間不會返回給作業系統，並且檔案的物理大小也不會更改。 因此，指定 NOTRUNCATE 時，資料庫似乎不會收縮。

NOTRUNCATE只適用於資料檔案。 NOTRUNCATE不影響日誌檔案。

TRUNCATEONLY

將檔案末尾的所有可用空間釋放給作業系統。 不移動檔案內的任何頁面。 資料檔案僅收縮到最後指定的盤區。 如果使用 TRUNCATEONLY 指定，則會忽略 target_percent。

TRUNCATEONLY 將影響日誌檔案。 若要僅截斷資料檔案，請使用 DBCC SHRINKFILE。

以下示例將縮小 UserDB 資料庫中資料檔案和日誌檔案的大小，以便在資料庫中留出 10% 的可用空間。

DBCC SHRINKDATABASE (UserDB, 10);

GO

收縮資料檔案的官方文件

DBCC SHRINKFILE

(

{ file_name | file_id }

{ [ , EMPTYFILE ]

| [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]

}

)

[ WITH NO_INFOMSGS ]

file_name | file_id

要收縮的檔案的邏輯名稱或標識 (ID) 號，參加sys.master_files檢視的name或file_id欄位。

target_size

整數，檔案的新大小(以 MB 為單位)。 如果未指定，DBCC SHRINKFILE 縮小到檔案建立大小。

NOTRUNCATE

無論是否指定 target_percent，將資料檔案末尾中的已分配頁移到檔案開頭的未分配頁區域中。 作業系統不會回收檔案末尾的可用空間，檔案的物理大小也不會改變。 因此，如果指定 NOTRUNCATE，檔案看起來就像沒有收縮一樣。 NOTRUNCATE 只適用於資料檔案。 日誌檔案不受影響。 FILESTREAM 檔案組容器不支援此選項。

TRUNCATEONLY

將檔案末尾的所有可用空間釋放給作業系統，但不在檔案內部移動任何頁。 資料檔案只收縮到最後分配的區。 如果使用 TRUNCATEONLY 指定，則會忽略 target_size。

TRUNCATEONLY 選項不會移動日誌中的資訊，但會刪除日誌檔案末尾的失效 VLF。 FILESTREAM 檔案組容器不支援此選項。

以下示例將 UserDB 資料庫中名為 DataFile1 的資料檔案的大小收縮到 10 MB。

USE UserDB;

DBCC SHRINKFILE (DataFile1, 10);

GO

檢視資料檔案的大小

select name,size*8/1024 MB from sys.master_files where database_id=db_id(N'DBNAME')

檢視資料檔案可收縮空間，結果見Availabesize_MB欄位值

select name ,size*8/1024 as Totalsize_MB ,CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 as Usedsize_MB,

size*8/1024 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)*8/1024 AS Availabesize_MB

from sys.master_files where database_id=db_id(N'DBNAME')

檢視收縮的進度100%，此語句要到指定的資料庫下執行

SELECT DB_NAME(database_id) AS Exec_DB

,percent_complete

,CASE WHEN estimated_completion_time < 36000000

THEN '0' ELSE '' END + RTRIM(estimated_completion_time/1000/3600)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%3600/60), 2)

+ ':' + RIGHT('0' + RTRIM((estimated_completion_time/1000)%60), 2) AS [Time Remaining]

,b.text as tsql

,*

FROM SYS.DM_EXEC_REQUESTS

cross apply sys.dm_exec_sql_text(sql_handle) as b

WHERE command LIKE 'DbccFilesCompact%' --and database_id=db_id('cardorder')

ORDER BY 2 DESC