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

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

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

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

3、收縮數(shù)據(jù)文件時(shí)，不要一次性全部收縮。 可以每次收縮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')來(lái)查看

5、收縮的100%進(jìn)度可以通過(guò)sys.dm_exec_requests的字段percent_complete來(lái)看

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

alter index all on table_name rebuild with (>

收縮數(shù)據(jù)庫(kù)的官方文檔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ù)庫(kù)名稱(chēng)或 ID。 0 指定使用當(dāng)前數(shù)據(jù)庫(kù)。

target_percent

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

NOTRUNCATE

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

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

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

TRUNCATEONLY

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

TRUNCATEONLY 將影響日志文件。 若要僅截?cái)鄶?shù)據(jù)文件，請(qǐng)使用 DBCC SHRINKFILE。

以下示例將縮小 UserDB 數(shù)據(jù)庫(kù)中數(shù)據(jù)文件和日志文件的大小，以便在數(shù)據(jù)庫(kù)中留出 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

要收縮的文件的邏輯名稱(chēng)或標(biāo)識(shí) (ID) 號(hào)，參加sys.master_files視圖的name或file_id字段。

target_size

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

NOTRUNCATE

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

TRUNCATEONLY

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

TRUNCATEONLY 選項(xiàng)不會(huì)移動(dòng)日志中的信息，但會(huì)刪除日志文件末尾的失效 VLF。 FILESTREAM 文件組容器不支持此選項(xiàng)。

以下示例將 UserDB 數(shù)據(jù)庫(kù)中名為 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é)果見(jiàn)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')

查看收縮的進(jìn)度100%，此語(yǔ)句要到指定的數(shù)據(jù)庫(kù)下執(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