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