合 MSSQL收缩数据文件和日志文件
Tags: MSSQLSQL Server脚本CDC发布订阅基础维护收缩数据文件收缩日志
- 简介
- 自动收缩
- 手动收缩数据库
- 手动收缩文件
- 通过将数据迁移到同一文件组中的其他文件来清空文件
- SQL Server的full和simple之间是什么区别
- SQL Server收缩数据文件需要注意什么
- 数据文件收缩后文件大小不变的原因
- DBCC SHRINKDATABASE 和 DBCC SHRINKFILE命令的区别有哪些
- 收缩数据文件和日志文件的方法
- 方法1:使用命令(推荐)
- 方法2:通过图形界面逐步操作
- 分批逐步收缩
- 日志收缩示例
- DBCC SHRINKFILE的选项
- target_size
- DBCC SHRINKFILE的TRUNCATEONLY选项
- 报错“不能收缩 ID 为 11 的数据库中 ID 为 1 的文件,因为它正由其它会话收缩或为空”
- 通过维护计划配置数据库自动收缩
- 数据库日志的log_reuse_wait_desc列为REPLICATION导致不能收缩
- 总结
- 参考
简介
事务日志记录着在相关数据库上的操作,同时还存储数据库恢复(recovery)的相关信息。收缩日志的原因有很多种,有些是考虑空间不足,有些则是应用程序限制导致的。
当数据库随着时间而越来越大时,可对数据库进行收缩操作;收缩数据文件通过将数据页从文件末尾移动到更靠近文件开头的未占用的空间来恢复空间,在文件末尾创建足够的空间后,可取消对文件末尾的数据页的分配并将它们返回给文件系统;
SQL Server 数据库支持自动收缩数据库和用户手动收缩数据库两种方式;
自动收缩
为提高数据库的使用效率,SQL Server 会寻找可用的数据库并找出第一个配置为自动收缩的数据库,它将检查该数据库,并在需要时收缩该数据库;待一个数据库收缩完成后,系统会等待几分钟再检查下一个配置为自动收缩数据库,即 SQL Server 不会同时坚持所有数据库,也不会同时收缩所有数据库,它将以循环方式处理各个数据库,以负载在时间上错开;若用户需要 SQL Server 数据库系统自动对数据库进行收缩,只需为该数据库设置自动收缩功能即可;操作步骤如下所示:
右击选择数据库 → 属性 → 在数据库属性对话框,选项 → 将自动收缩设置为 TRUE;
手动收缩数据库
用户还能手动对数据库进行收缩,但手动收缩数据库有一定的限制和局限;主要的表现有如下几个方面:
- 收缩后的数据库不能小于数据库的最小大小 ;最小大小在数据库创建时指定的大小,或者上一次使用文件大小更改操作设置的大小;
- 不能在备份数据库时收缩数据库;反之,也不能在数据库执行收缩时备份数据库;
- 遇到内存游湖的列存储索引时,DBCC SHRINKDATABASE 操作将会失败;遇到 columnstore 索引之前完成的工作将会成功,所以数据库可能会较小;若要完成 DBCC SHRINKDATABASE,则需要执行 DBCC SHRINKDATABASE前禁用所有列的存储索引,再重新生成列存储的索引;
手动收缩数据库可在 SSMS 工具的对象资源管理器中完成,具体步骤如下:
展开数据库,右击要收缩的数据库;
任务 → 收缩 → 单击数据库
在收缩数据界面展示的是数据库 srs 的基本信息,若需要进行收缩操作,需要先选中“在释放未使用的空间前重新组织文件。选中此项可能会影响性能(R)”,再收缩后文件中的最大可用空间中选择收缩后的空间,单击确定,即可完成手动收缩数据库操作;
手动收缩文件
- 手动收缩文件跟手动收缩数据库操作类似;
在 SSMS 工具对文件的对象资源管理器中完成,具体步骤如下:
展开数据库,右击数据库;任务 → 收缩 → 单击文件
在收缩文件界面,在收缩操作中,可选择释放未使用空间、在释放未使用空间前重新组织页、通过将数据迁移到同一文件组中的其他文件来清空文件,三选一来进行手动收缩文件操作;
通过将数据迁移到同一文件组中的其他文件来清空文件
从指定文件迁移所有数据。 此选项允许使用 ALTER DATABASE 语句删除文件。 此选项等效于执行带有 EMPTYFILE 选项的 DBCC SHRINKFILE。
1.对数据库创建一个新的文件FileB,文件组为GroupA
2.使用数据库/任务/收缩/文件,选中FileA ,勾选择:通过数据迁移到同一文件组中的其他文件来清空文件
可能会报错“无法将文件 "lhrdb" 的所有内容移到其他位置,以完成清空文件操作。” 没有好的办法解决。。。。
3.等待……
4.收缩完成,执行ALTER DATABASE [数据库名称] REMOVE FILE [FileA]
问题在这一步出来,报错消息:无法对具有数据库快照或活动 DBCC 副本的数据库执行此操作。
解决办法:把该数据库的快照删除掉,再执行第4步,成功的把FileA文件删除掉。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | SELECT type_desc,NAME, size, a.physical_name FROM sys.database_files a; GO select * from sys.filegroups fg; ALTER DATABASE lhrdb ADD FILE ( NAME = lhrdb_new, -- 数据文件逻辑名称 FILENAME = '/var/opt/mssql/data/lhrdb_new.mdf', -- 数据文件的物理路径 SIZE = 100MB, -- 数据文件的初始大小 MAXSIZE = UNLIMITED, -- 数据文件的最大大小 FILEGROWTH = 10MB -- 数据文件的增长大小 ) TO FILEGROUP [PRIMARY]; USE [lhrdb] GO DBCC SHRINKFILE (N'lhrdb' , EMPTYFILE) GO |
SQL Server的full和simple之间是什么区别
SQL Server 中的“full”和“simple”是指数据库恢复模型,这两种模型决定了数据库如何进行日志记录和恢复。下面是它们之间的区别:
- Simple模型:在简单恢复模式下,SQL Server仅保留当前活动事务的日志信息,一旦这些事务提交,相应的日志记录将被清除。这意味着,如果出现故障,只能将整个数据库还原到最近备份的状态,无法使用全量备份+日志追加恢复到最新时刻。简单恢复模式适用于仅需要在备份之间进行最小的数据丢失的数据库。
- Full模型:在完整恢复模式下,SQL Server将保留所有事务的日志信息,包括已提交和未提交的事务。这允许数据库管理员使用日志备份文件逐步恢复数据库到发生故障之前的某个时间点。完整恢复模式适用于要求最小数据丢失和最小停机时间的数据库。
总之,简单恢复模式提供了更少的恢复选项,但通常比完整恢复模式需要更少的磁盘空间,而完整恢复模式可以提供更高级的恢复选项,但需要更多的磁盘空间。因此,应该根据数据库的需求和负担来选择适当的恢复模式。
SQL Server收缩数据文件需要注意什么
在 SQL Server 中,当数据库的数据文件(.mdf)中存在大量未使用的空间时,可以使用收缩操作将文件大小减小,从而节省磁盘空间。但是,在执行收缩操作时需要注意以下几点: