sql server收缩数据库，收缩日志文件与数据文件

【1】收缩概述

收缩数据文件概述：

　　数据库使用数据文件（扩展名是mdf 或 ndf)来存储数据，使用日志文件（扩展名是ldf）来存储事务日志，通常情况下，数据文件会持续增长，不会自动释放空闲空间，这样会导致硬盘空间耗尽。如果一个数据库的文件有很多空闲空间，收缩数据库文件是一种解决硬盘空间紧张的直接方式。在SQL Server中，我们可以使用 DBCC ShrinkFile命令收缩数据文件，该命令首先将文件尾部的区（extent）移动到文件的开头，文件结尾的空闲的硬盘空间被释放给操作系统，这种操作就像截断将文件的尾部一样，这种方式不需要消耗很多IO就能释放空间；但是，如果空闲部分不在文件末尾时，收缩操作必须扫描数据文件，并对正在读取的页面加锁，把文件末尾的区移动到文件开头，这是一个IO密集型的操作，影响数据库的性能，但是，收缩操作不是一个独占行为，在做文件收缩时，其他用户仍然可以对数据库进行读写操作。在任意一个时间点停止dbcc shrinkfile命令，任何已经完成的工作都将保留。

收缩数据库是非常耗费server性能的操作，如果没有必要不要收缩
go
　　收缩无非就是把已经分配给数据库文件空间收回来，但是，收缩的时候要移动数据页，而且可能造成大量的碎片，影响性能。
go
　　日志收缩和数据文件收缩不一样，日志中的虚拟文件状态只有在可复用时候才能收缩。
　　而且凡是有活动的日志的日志虚拟文件都是活动的不能收缩。
go

收缩日志概述：

日志文件收缩，回收磁盘空间。
　　数据文件根据设计设计的大小，一般情况不用收缩，收缩可能带来性能的问题。

日志
　　如果选择的日志恢复模型是完全，如果没有日志截断，日志增长的很大，
建议
　　备份日志。
　　　　backup log db to disk='备份设备'
　　截断日志
　　　　backup log db with no_log
　　然后收缩。
　　　　dbcc shrinkfile(2,10)

原理参考：https://www.cnblogs.com/gallen-n/archive/2017/03/15/6555283.html

【2】关于收缩数据库的建议

不到万不得已，千万不要收缩数据库。收缩数据库影响极大：

1.收缩数据库对数据库的影响极大，产生大量日志和碎片，而且会锁表。如果你的库当前正在被使用，收缩不下去非常正常。
2.收缩数据库一定要手工来做的，而且是在维护窗口期做的事。
3.尽量使用语句来执行，可以提示错误

下面的文章详细介绍：
http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/.

【3】收缩的正确姿势

在不得不收缩的时候，参考下面的步骤

【3.1】truncateonly

1.找到数据库中最大的几个表，重建所有索引。首先尝试指定Truncate Only收缩方式.它只是移除文件尾部的空闲空间，并不重新组织已经使用的数据页。

DBCC SHRINKDATABASE (AdventureWorks2012, TRUNCATEONLY);

【3.2】每次收缩很少，之后重建索引

2 最后才考虑，不带选项的收缩。收缩不要一次性全部收缩。可以每次收缩2G左右。不要把空间可用空间全部收缩了，可以剩余一部分比如4G。收缩完后，记得重建索引.

补充：

还有一种办法就是新建文件组，使用CREATE INDEX ... WITH（DROP_EXISTING = ON）ON语法将所有相关的的表和索引移动到新文件组。然后收缩旧的文件组。

--收缩数据文件，缩成100M
USE [Db_Del]
GO
DBCC SHRINKFILE (N'Db_Del_Data' , 100)
GO

--收缩数据库，缩成原有90%，但会造成页迁移与索引碎片
USE [Db_Del]
GO
DBCC SHRINKDATABASE(N'Db_Del', 90 )
GO


--重建表索引
ALTER INDEX ALL ON Employee
REBUILD WITH (FILLFACTOR = 60, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);

【3.3】收缩过程中可以随时中止

3.可在进程中的任一点停止 DBCC SHRINKDATABASE 操作，任何已完成的工作都将保留。

4.不能在备份数据库时收缩数据库

【4】可能需要收缩的场景

1.你删除了大量数据，而且数据不太可能增长。

2.要移除某个文件时，你需要先清空数据文件。

总结

那我们处理磁盘空间不足的最好的办法是什么呢？最好的办法是在最初规划时，预估好未来一年或者二年的数据增长。给磁盘划分足够的空间。设置好数据库的初始大小，并且将自动增长使用固定量增长。

作者：Owen Zeng

出处：https://www.cnblogs.com/OwenZeng/

如果您觉得阅读本文对您有帮助，请点一下“推荐”按钮，您的“推荐”将是我最大的写作动力！欢迎各位转载，转载文章之后必须在文章页面明显位置给出作者和原文连接，否则保留追究法律责任的权利。

【实践参考】

--===================================DB_Tank
USE [master]
GO

ALTER DATABASE [DB_Tank] SET RECOVERY SIMPLE WITH NO_WAIT
GO

EXEC('
USE [Db_Tank]
DBCC SHRINKFILE (''Db_Tank_Log'' , 2048)  --sql server默认文件单位为M，所以这里是2G
')

print '【'+CONVERT(varchar(20),GETDATE(),120)+'】恢复游戏库模式'
GO

USE [master]
GO

ALTER DATABASE [Db_Tank] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO


--====================================DB_logs
USE [master]
GO

ALTER DATABASE [DB_logs] SET RECOVERY SIMPLE WITH NO_WAIT
GO

EXEC('
USE [Db_logs]
DBCC SHRINKFILE (''Db_Logs_Log'' , 2048)
')

print '【'+CONVERT(varchar(20),GETDATE(),120)+'】恢复游戏库模式'
GO

USE [master]
GO

ALTER DATABASE [Db_Logs] SET RECOVERY BULK_LOGGED WITH NO_WAIT
GO


--=======================================系统日志
/*
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG
里面的 ERRORLOG.1 错误日志很大，轮转掉它

sp_cycle_errorlog -手动启动轮转系统日志的sp

*/
/*
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
tempdb 磁盘收缩

*/

--======================================tempdb
USE [tempdb]
GO
DBCC SHRINKFILE (N'templog' , 1024)
GO

USE [master]
GO

print '【'+CONVERT(varchar(20),GETDATE(),120)+'】收缩日志成功' 
GO

print '【'+CONVERT(varchar(20),GETDATE(),120)+'】正在收缩数据库' 
GO
DBCC SHRINKDATABASE(tempdb)
GO
print '【'+CONVERT(varchar(20),GETDATE(),120)+'】收缩数据库成功'

View Code

【5】收缩数据库文件

一，收缩文件命令

收缩文件（DBCC SHRINKFILE）命令做的收缩都是区一级的动作，它会把使用过的区向文件前面移动，把没有使用的区从文件中移除。但是，它不会把一个区里面的空闲页面(Empty Page)移除、也不会把页面中的空闲空间移除。因此，如果一个数据库文件中含有很多只使用了一两个页面的区，收缩操作的效果会不明显。收缩文件不会丢失数据，如果数据最少占用1GB空间，那么收缩操作不会把文件收缩到少于1GB。

收缩文件命令的语法：

DBCC SHRINKFILE 
(
    { file_name | file_id } 
    { [ , EMPTYFILE ] 
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]
    }
)

收缩文件是一个函数，其参数的含义是：

target_size ：单位是MB，收缩操作的目标是把文件收缩到target_size指定的大小。收缩操作是把文件末尾的区向文件开头移动，这意味着，文件末尾的已使用的区会被移动到文件的前面，先重新分配可用的硬盘空间，在移动数据之后，释放数据原来占用的空间。
EMPTYFILE ：清空文件，把数据从当前文件迁移到同一个文件组中的他文件上，同时禁止数据存储到空文件中，被清空的文件被移除。
NOTRUNCATE ：不截断选项，该选项的作用是把已分配（allocated）的区从文件末尾移动到文件前面的未分配的（unallocated ）区中，由于文件末尾未的空闲空间并没有释放到操作系统，因此文件的大小不变。
TRUNCATEONLY ：仅截断选项，该选项的作用是把文件末尾的空闲空间释放给操作系统，由于该选项不会做任何区的移动操作，因此，该选项不会消耗硬盘IO，操作的结果是数据文件的大小减少。在截断日志文件时，该选项不移动任何日志记录，仅仅从日志文件的末尾移除不活动的虚拟日志文件（VLF）。

二，收缩文件的步骤

通常情况下，收缩数据库的数据文件和日志文件，释放文件中的空闲空间，需要两个步骤：先移动，后释放

Step1，将文件的后部分中正在使用的区向前移动，移动到文件的前部分：

dbcc shrinkfile('filename',0,notruncate)

Step2，将文件末尾未被使用的区释放掉，归还给操作系统

dbcc shrinkfile('filename',target_size_MB,truncateonly)

清空文件，是为了把空文件从数据库中移除：

-- Empty the data file.
DBCC SHRINKFILE (Test1data, EMPTYFILE);
GO
-- Remove the data file from the database.
ALTER DATABASE AdventureWorks2012
REMOVE FILE Test1data;
GO

三，日志文件的收缩

事务日志文件不停增长的原因有很多，比如

1，Recovery Mode 不是simple，或者说不处于auto-truncate 模式下，
2，有一个很大的事务在运行，
3，有一个运行很长时间的事务没有提交

都会导致事务日志文件不停增长，此时执行dbcc shrinkflie 命令是不能把日志文件shrink的，必须针对特定的情况，采取对应的对策，一般来说，

对于1，必须安排backup log的计划，使事务日志文件截断和重用，
对于2，优化语句的业务逻辑，避免出现大的事务，
对于3，优化application，及时commit 或 rollback 事务。

有时，会遇到日志文件无法清空的异常：

dbcc shrinkfile('db_log',emptyfile)

四，收缩操作的副作用

收缩数据文件是IO密集型的操作，需要IO，CPU和内存资源，并会产生大量的事务日志，并会导致索引碎片，因此，尽量避免收缩数据文件，只有当迫不得已时，才适当收缩数据文件。由于数据文件的增长也是非常耗时的，所以，不要把数据文件收缩得太小，要预留足够的空间，避免数据文件出现增长的情况。

1，影响Range操作的查询性能

Shrink file 有可能产生大量的碎片，这是因为shrink file 存在Extent的移动，逻辑上连续的extent，在移动之后，可能会导致物理上不再连续，影响Range 操作的性能。

2，消耗IO和CPU资源

如果SQL Server在shrink data file时不移动数据，那么shrink 就不会产生碎片，对现有数据不会有影响。唯一不影响现有物理数据的情况是在执行DBCC Shrinkfile 命令时指定TruncateOnly选项，DBCC Shrink命令只将文件末尾的剩余空间释放，归还给OS。

Shrinkfile 是个非常消耗IO资源的操作，Shrinkfile的过程需要移动大量的数据，消耗大量的IO；Shrinkfile的过程会被记录到日志，造成日志暴涨；还会消耗大量的CPU资源。

3，产生大量的日志，影响依赖日志的Application的性能

收缩产生的大量日志会被事务日志传送，镜像，复制等操作重复执行。

4，ShrinkFile 不能将Data File 收缩的太小

当插入新的数据时，如果Data File空间不够，那么SQL Server需要重新申请 Disk Space，增加 Data file Size，在此过程中，SQL Server需要对新分配的Disk Space填0初始化，除非你开启的是不用填零初始化的选项，不用填零初始化有泄露信息的风险。在全部空间初始化完成之前，新分配的Data File是不能使用的。这个填0初始化，是个非常耗费IO资源的操作，如果一次增长的空间过大，SQL Server需要很长时间对新分配的Disk Space进行初始化，这可能会导致Application timeout，使事务回滚，影响Application的性能。

参考文档：

DBCC SHRINKFILE (Transact-SQL)

Why you want to be restrictive with shrink of database files

出处：http://www.cnblogs.com/ljhdo/

收缩文件命令详细解析：https://www.cnblogs.com/gered/p/9366256.html

posted @ 2018-05-03 10:15 郭大侠1 阅读(4636) 评论(0) 编辑收藏举报

刷新页面返回顶部