一些实际工作中的总结
创新互联公司是一家专业提供文圣企业网站建设,专注与成都网站制作、网站设计、H5建站、小程序制作等业务。10年已为文圣众多企业、政府机构等服务。创新互联专业网站制作公司优惠进行中。
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