ITPub博客

首页 > 数据库 > SQL Server > sqlserver收缩数据库、收缩数据文件的操作

sqlserver收缩数据库、收缩数据文件的操作

原创 SQL Server 作者:lusklusklusk 时间:2019-09-10 16:27:20 0 删除 编辑

一些实际工作中的总结

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2656642/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。11G OCM, 8年以上DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    374
  • 访问量
    491475