sqlserver收縮數(shù)據(jù)庫、收縮數(shù)據(jù)文件的操作

一些實際工作中的總結(jié)

創(chuàng)新互聯(lián)公司是一家專業(yè)提供文圣企業(yè)網(wǎng)站建設,專注與成都網(wǎng)站制作、網(wǎng)站設計、H5建站、小程序制作等業(yè)務。10年已為文圣眾多企業(yè)、政府機構(gòu)等服務。創(chuàng)新互聯(lián)專業(yè)網(wǎng)站制作公司優(yōu)惠進行中。

1、實際工作中收縮數(shù)據(jù)文件的情況比收縮庫的情況多，不建議直接收縮數(shù)據(jù)庫

2、收縮很容易出現(xiàn)等待，收縮會話對應sys.sysprocesses的字段waitresource值類似為15:1:4700649，sys.sysprocesses的字段lastwaittype值為PAGEIOLATCH_SH或PAGEIOLATCH_EX等

3、收縮數(shù)據(jù)文件時，不要一次性全部收縮。 可以每次收縮5G左右，比如DataFile1有32G，則每次收縮如下

USE UserDB;

DBCC SHRINKFILE (DataFile1, 27000);

GO

DBCC SHRINKFILE (DataFile1, 22000);

GO

4、數(shù)據(jù)文件的可用空間可以結(jié)合sys.master_files和FILEPROPERTY(name,'SpaceUsed')來查看

5、收縮的100%進度可以通過sys.dm_exec_requests的字段percent_complete來看

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

alter index all on table_name rebuild with (>

收縮數(shù)據(jù)庫的官方文檔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

要收縮的數(shù)據(jù)庫名稱或 ID。 0 指定使用當前數(shù)據(jù)庫。

target_percent

整數(shù)，數(shù)據(jù)庫收縮后的數(shù)據(jù)庫文件中所需的剩余可用空間百分比。

NOTRUNCATE

將分配的頁面從文件的末尾移動到文件前面的未分配頁面。 此操作會壓縮文件中的數(shù)據(jù)。

文件末尾的可用空間不會返回給操作系統(tǒng)，并且文件的物理大小也不會更改。 因此，指定 NOTRUNCATE 時，數(shù)據(jù)庫似乎不會收縮。

NOTRUNCATE只適用于數(shù)據(jù)文件。 NOTRUNCATE不影響日志文件。

TRUNCATEONLY

將文件末尾的所有可用空間釋放給操作系統(tǒng)。 不移動文件內(nèi)的任何頁面。 數(shù)據(jù)文件僅收縮到最后指定的盤區(qū)。 如果使用 TRUNCATEONLY 指定，則會忽略 target_percent。

TRUNCATEONLY 將影響日志文件。 若要僅截斷數(shù)據(jù)文件，請使用 DBCC SHRINKFILE。

以下示例將縮小 UserDB 數(shù)據(jù)庫中數(shù)據(jù)文件和日志文件的大小，以便在數(shù)據(jù)庫中留出 10% 的可用空間。

DBCC SHRINKDATABASE (UserDB, 10);

GO

收縮數(shù)據(jù)文件的官方文檔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

整數(shù)，文件的新大小(以 MB 為單位)。 如果未指定，DBCC SHRINKFILE 縮小到文件創(chuàng)建大小。

NOTRUNCATE

無論是否指定 target_percent，將數(shù)據(jù)文件末尾中的已分配頁移到文件開頭的未分配頁區(qū)域中。 操作系統(tǒng)不會回收文件末尾的可用空間，文件的物理大小也不會改變。 因此，如果指定 NOTRUNCATE，文件看起來就像沒有收縮一樣。 NOTRUNCATE 只適用于數(shù)據(jù)文件。 日志文件不受影響。 FILESTREAM 文件組容器不支持此選項。

TRUNCATEONLY

將文件末尾的所有可用空間釋放給操作系統(tǒng)，但不在文件內(nèi)部移動任何頁。 數(shù)據(jù)文件只收縮到最后分配的區(qū)。 如果使用 TRUNCATEONLY 指定，則會忽略 target_size。

TRUNCATEONLY 選項不會移動日志中的信息，但會刪除日志文件末尾的失效 VLF。 FILESTREAM 文件組容器不支持此選項。

以下示例將 UserDB 數(shù)據(jù)庫中名為 DataFile1 的數(shù)據(jù)文件的大小收縮到 10 MB。

USE UserDB;

DBCC SHRINKFILE (DataFile1, 10);

GO

查看數(shù)據(jù)文件的大小

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

查看數(shù)據(jù)文件可收縮空間，結(jié)果見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%，此語句要到指定的數(shù)據(jù)庫下執(zhí)行

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

網(wǎng)頁名稱：sqlserver收縮數(shù)據(jù)庫、收縮數(shù)據(jù)文件的操作
文章起源：http://m.jiaotiyi.com/article/ijipse.html