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 (>

收縮數據庫的官方文檔https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql?view=sql-server-2017

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

收縮數據文件的官方文檔https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

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