文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

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

2024-04-02 19:55

关注

一些实际工作中的总结

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

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯