（3.5）sql server存储引擎--事务日志与数据文件
一. 日志结构
1.1、物理日志
（0）物理日志即数据库的.ldf文件，当然后缀名是可以自定义的，默认是.ldf；
（1）一个SQL SERVER数据库，可以定义多个物理日志文件，SQL SERVER逻辑上把它们当成一个整体，顺序写入日志纪录，用完第一个再用下一个：即第一个日志文件的当前空间，如果没有可分配的VLF时，就会使用下一个日志文件的VLF，直到最后一个日志文件也没有可分配的VLF时，会重新回到第一个日志开始增长；VLF的使用如下图：
（2）物理日志文件初始大小至少为512KB；
（3）日志文件不可以放在文件组内；
1.2、虚拟日志
（0）日志文件除了文件头页外，其他VLF部分都不是以数据页的方式来存储的，物理日志以虚拟日志（VLF）为最小单位进行增长、收缩和使用，通常VLF大小至少为256KB，但第一个VLF的大小最小为256K-8K，因为第一个页面8K为日志文件头页面；
（1）虚拟日志是由SQL SERVER来维护的，大小不一，数量不定，不可以人工干预，但可以事先分配较大的物理日志，或者设置较大的物理日志增量，以减少虚拟日志的生成，从而减少数据库维护虚拟日志的成本、以及提高数据库启动及备份还原的速度；
注意：如果设置日志文件的增量过小，则会产生过多的VLFS,也就是日志文件碎片，过多的日志文件碎片会拖累SQL Server性能.
SQL Server创建数据库时，根据日志文件(ldf)的大小，生成VLF的数量公式如下:（可以通过dbcc loginfo(dbname)来查看数据库日志文件的VLF数量）
|
ldf文件的大小 |
VLF的数量 |
|
1M到64M |
4 |
|
64M到1GB |
8 |
|
大于1GB |
16
|
1.3、逻辑日志
（0）数据库逻辑操作的记录，每个事务可能会有多条日志纪录，每条日志记录由唯一的顺序增长的LSN来标记；
通过DBCC LOG()来查看日志文件如下图：或者通过fn_dblog(null,null)
（1）SQL SERVER数据库是不可能不记录日志的，它要用日志来保证事务的基本属性、及数据库恢复。同时也没有类似ORACLE的NOLOGGING开关（并非真正无日志），最相似的应该就是BULK_LOGGED恢复模式；
（2）前面说到过，SQL SERVER数据库遵循预写日志（WAL）的原则；
1.4、活动日志
（0）从MinLSN起往后的日志部分即为活动日志；如下图，以最早活动事务起点的LSN142作为MinLSN，从142起往后的日志部分为活动日志：
（1）检查点LSN、最早活动事务起点的LSN、尚未传递给分发数据库的最早的复制事务起点的 LSN，当中的最小值将作为MinLSN；
二. 日志管理
2.1、截断
（0）SQL SERVER可以通过截断日志以实现物理日志的回绕，截断操作仅是将被截断的日志部分标记为可重用。
根据数据库恢复模式的设置：SIMPLE/BULK_LOGGED/FULL，在SIMPLE模式下，SQL SERVER会自动截断日志，类似于ORACLE中的非归档模式；
（1）SIMPLE模式下，CHECKPOINT会自动截断日志的非活动部分，FULL和BULK_LOGGED模式下，备份日志的TRUNCATE_ONLY/NO_LOG选项在2008的版本中已不再支持，只有通过日志备份来截断日志；
（2）SQL SERVER 截断日志后，并不会主动释放日志文件占用的磁盘空间，需要手动去收缩日志文件才会释放，但通常不建议这样做，毕竟当日志文件再次增大时又需要去重新申请磁盘空间；
（3）日志文件的截断以VLF为单位，从不活动的日志纪录所在的第一个VLF起，到MinLSN所在的VLF的前一个VLF，如下图：
（4）只可以截断非活动的日志部分；
（5）当运行一个长事务且一直未结束时，此时会影响MinLSN的推进，进而影响日志文件的截断，从而会出现，即便是在SIMPLE模式下，日志文件也会变得很大，甚至出现吃掉磁盘所有空间，出现事务日志已满的9002错误。
2.2、备份
（0）SQL SERVER没有ORACLE中的ARCH进程，无法像ORACLE一样自动归档日志，需要去手动备份，而且在有多个物理日志文件时，也无法对单个日志文件进行备份；
（1）当数据库故障恢复时，在线的日志需要手动通过NO_TRUNCATE选项去备份，即尾日志备份，然后再利用尾日志备份结合之前的备份进行故障恢复。
2.3、还原
还原在两种情况下发生，一是数据库重启时，以下简称重启恢复；一是手动通过备份集恢复时，以下简称介质恢复。
（0）还原的过程，是把数据和日志放在内存中，模拟用户读写操作以进行的。还原时只需要重做或撤消最后一个检查点之后的日志部分，这也是检查点机制提高恢复效率的原因所在；
（1）如果事务日志已结束（提交或回滚），而且数据页尚未被刷新，则重做（REDO）；如果事务日志未结束，但数据脏页已被刷新到磁盘，则回滚（UNDO）；
（2）日志记录中包含数据页被修改前及当前修改的两个LSN，如果目前数据页头的LSN等于修改前的LSN，则日志操作被重做，如果数据页头的LSN等于或大于当前修改的LSN，则跳过日志操作，不重做；
（3）重启恢复不需要人工干预，介质恢复需要手工干预，因为需要逐个应用日志备份，可以利用日志备份，将数据库恢复到某个具体的时间点/日志点。
这里描述的较为概念化，具体可以参考宋大侠的几篇文章（Careyson）
SQL日志
浅谈SQL Server中的事务日志(一)----事务日志的物理和逻辑构架
浅谈SQL Server中的事务日志(二)----事务日志在修改数据时的角色
浅谈SQL Server中的事务日志(三)----在简单恢复模式下日志的角色
浅谈SQL Server中的事务日志(四)----在完整恢复模式下日志的角色
浅谈SQL Server中的事务日志(五)----日志在高可用和灾难恢复中的作用
本文转自：http://blog.51cto.com/qianzhang/1217394
参考：careySon的：浅谈SQL Server中的事务日志(一)----事务日志的物理和逻辑构架
其他文章描述
1 事务日志基本介绍
每个数据库都具有事务日志，用于记录所有事物以及每个事物对数据库所作的操作。
日志的记录形式需要根据数据库的恢复模式来确定，数据库恢复模式有三种：
- 完整模式，完全记录事物日志，需要定期进行日志备份。
- 大容量日志模式，适用于批量操作的数据库，可以以更压缩的方式处理日志，需要定期进行日志备份。
- 简单模式，也有日志文件，只是该模式下可以通过checkpoint自动重用virtual log file，所以日志文件会处于一直重复使用的过程，保持一定大小，但是，如果有一个事务启动，很久没有commit，那么从这个事务开始到最后commit的时间段内的事务日志存储空间都无法checpoint自动重用，这时，你很可能看到一个很大的日志文件；注意，简单模式下是无法进行日志备份。
数据库里边，任何对数据库的读写都是在内存页中找到对应的数据也，再做修改，如果内存页中不存在数据页，则从磁盘加载如内存中。当一个修改操作发生时，修改的将是内存页中对应的数据页面，同时也会实时记录到日后文件ldf中。那么，什么时候数据会被同步到mdf文件呢，只有以下三种情况：
- 做checkpoint时，后续会专门整理checkpoint的相应文章；
- Lazy write运行时，即内存出现压力，需要把内存中的数据页写入到磁盘，腾出内存空间；
- eager write时，即发生bulk insert和select into操作时。
DB中的事务日志记录，可以给我们带来很多好处，它可以支持以下操作：
- 恢复个别的事务。
- 在 SQL Server 启动时恢复所有未完成的事务。
- 将还原的数据库、文件、文件组或页前滚至故障点。
- 支持事务复制。
- 支持高可用性和灾难恢复解决方案：AlwaysOn 可用性组、数据库镜像和日志传送。
2 对数据库启动的影响
当数据库重启或者还原到最后的时候，数据库都会进入 recovery状态，正常情况下，这个状态持续时间在几十秒间，但是特殊情况下，它会花费非常长的时间，甚至几个小时，如果这个步骤失败，数据库则进入到挂起 suspect状态，无法正常提供使用。
那么，当数据库进入 recovery 的时候，它在操作些什么呢？
SQL SERVER日志会记录所有修改记录（数据的修改情况，不包含SQL语句），包括Begin Transaction和Commit / Rollback Transaction 操作。由于对事务日志的修改，要比数据文件的修改要快，所有会出现，数据修改更新到了日志文件，但是还没有落盘到数据文件，那么这个时候数据库就处于recovery状态，同时对事务日志最近的一个checkpoint点以后的所有数据修改记录做以下检查：
所有检查结束后，则会对数据库做一个checkpoint的表示，并写入事务日志中，表明日志文件跟数据文件已经同步结束，完成了recovery过程，数据库可正常提供使用。这里需要注意一点，如果你数据库最近一次checkpoint到现在的修改操作足够多，那么将会耗费相对较长时间来检查，同时也能够在 error log中看到百分比标识的recovery完成进展，避免漫无目的的等待。
Error Log的检查，可以通过图形界面（见下图）查看当前日志，也可以运行xp_readerrorlog 查询。
1 /* 2 xp_readerrorlog参数说明 3 1. 存档编号 4 2. 日志类型(1为SQL Server日志,2为SQL Agent日志) 5 3. 查询包含的字符串 6 4. 查询包含的字符串 7 5. LogDate开始时间 8 6. LogDate结束时间 9 7. 结果排序,按LogDate排序(可以为降序"Desc" Or 升序"Asc")，默认升序 10 */ 11 12 Exec xp_readerrorlog 0,1,Null,Null,'2017-02-16 10:53:32.300','2017-02-16 12:53:32.300'
假设出现这种情况，由于上线的重要程度远远重要过 数据丢失的情况，并且你跟所有部门沟通确认 可以承担 data file跟log file之间的差异数据的丢失，那么你可以按以下步骤操作，严重建议不要这么操作，因为会带来不可预估的数据丢失情况，如果你命悬一线，真打算放弃这部分数据，那么，可以按照以下操作：
1 #设置数据库单用户
2 alter database backupdb set single_user with rollback immediate
3
4 #设置数据库紧急状态
5 alter database backupdb set emergency with rollback immediate
6
7 #获取事务日志的物理名和逻辑名后，重建日志文件
8 select name,physical_name from sys.master_fiels where database_id=db_id('backupdb')
9 alter database backupdb rebuild log on (name='事务日志的逻辑名',filename='事务日志的物理名词')
10
11 #设置数据库online
12 alter database backupdb set online with rollback immediate
13
14 #设置数据库为多用户
15 alter database backupdb set multi_user with rollback immediate
3 日志文件添加方式
日志记录在 后缀名为 ldf的文件，允许有多个日志文件，但是不会并发分开记录日志，而是使用填充满一个日志文件后，再转向一个日志文件，线性操作日志文件。
可以通过下方来添加 ldf文件，需要注意几个地方：
- 初始大小，建议直接设置为 截断日志的期间内最大值，比如，某DB 恢复模式是完整模式，每隔半个小时做一次事务日志备份且截断日志，那么设置 日志文件大小的时候，取业务高峰期 每半小时的日志增长 最大值是5G，则可设置初始大小为 5G-7G之间；
- 增长大小，无论是 按比例增长还是按照MB增长，都不要设置过小，建议每次增长在100Mb左右，减少使用到自动增长，在最初设置的初始大小就满足其增长需求 ，如果开始设置的 初始大小 偏小，不满足，可以挑一个业务低峰期，修改变大初始大小。每一次文件自动增长期间，都会对写入的日志造成堵塞，虽然时间很短，但是如果增长频繁，则会影响数据库操作；
- 自动增长，建议设置为自动增长，但是前提定期监控日志的增长情况，避免磁盘空间不足，同时，如果恢复模式是 完整模式或者大容量模式，还需定期做日志备份截断日志，避免 事务日志已满的9002错误；
- 路径选择，建议与 mdf 文件放在不同的磁盘上，分散IO，若是磁盘读写瓶颈不大，则可放在一个磁盘上；
添加方式有2种，如下：
1 USE [master] 2 GO 3 ALTER DATABASE [backupdb] 4 ADD LOG FILE ( 5 NAME = N'backupdb_log_1', 6 FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\backupdb_log_1.ldf' , 7 SIZE = 524288KB , 8 MAXSIZE = 1048576KB , 9 FILEGROWTH = 10240KB 10 ) 11 GO
4 物理结构
数据库的事务日志映射在一个或者多个的物理文件上，从概念上讲，日志文件是一系列的日志记录；从物理上讲，日志记录序列被有效的存储在实现事务日志的物理文件中。
SQL Server 数据库引擎在内部将每一物理日志文件分成多个虚拟日志文件，即VLF（Virtual Log File），虚拟日志文件没有固定大小，且物理日志文件所包含的虚拟日志文件数不固定。数据库引擎在创建或扩展日志文件时动态选择虚拟日志文件的大小。数据库引擎尝试维护少量的虚拟文件。在扩展日志文件后，虚拟文件的大小是现有日志大小和新文件增量大小之和。
只有当日志文件使用较小的 size 和 growth_increment 值定义时，虚拟日志文件才会影响系统性能。如果这些日志文件由于许多微小增量而增长到很大，则它们将具有很多虚拟日志文件。这会降低数据库启动以及日志备份和还原操作的速度。建议您为日志文件分配一个接近于最终所需大小的size值，并且还要分配一个相对较大的 growth_increment 值。
管理员不能配置或设置虚拟日志文件的大小或数量，但是在VLF影响系统性能的情况下，可以尝试缩小，通过收缩日志文件的方式。
dbcc loginfo(dbname) 返回的行数，即为 VLF 文件个数，当status为0时，即该文件没有被使用，还能写入LOG，2表示已被使用，并且无法重用，这个时候，可以通过 backup Log 的形式，备份并截断LOG文件，则可以回收 从最后一个0到最近一个2行的空间。
收缩日志文件或者减少VLF文件的方式如下，需要先备份日志文件，才可以有效进行收缩，在没有备份日志文件的情况下，进行收缩，效果不大。详见以下代码：
1 #其行数及为VLF个数，status为0表示文件未用，为2表示已被使用，无法重用 2 dbcc loginfo 3 4 #备份日志 5 BACKUP LOG [backupdb] 6 TO DISK = N'D:\data\20170215_backupdb_log.trn' WITH NOFORMAT, NOINIT, NAME = N'backupdb-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10 7 GO 8 9 #收缩日志文件，根据日志文件名来收缩500Mb，建议收缩大小是合理大小，参考上文的 初始大小 判断 10 USE [backupdb] 11 GO 12 SELECT name FROM sys.database_files WHERE type_desc='log' 13 DBCC SHRINKFILE (N'jiankong_db_log' , 500) 14 GO 15 16 #其行数及为VLF个数，VLF文件减少 17 dbcc loginfo
事务日志是一种回绕的文件。假设，数据库backupdb只有一个ldf文件，且刚好分成了5个虚拟日志，当我们开始使用数据库的时候，逻辑日志从物理日志的最开始端向末端记录，如下图。
当出现checkpoint的时候，则会标注 最小恢复日志序列号 MinLSN，“MinLSN”是成功进行数据库范围内回滚所需的最早日志记录的日志序列号。如下图。
在MinLSN之前的所有虚拟日志文件VLF都可以被截断，数据库会在以下两个事件后自行截断日志：
-
简单恢复模式下，在检查点之后发生。
- 在完整恢复模式或大容量日志恢复模式下，如果自上一次备份后生成检查点，则在日志备份后进行截断（除非是仅复制日志备份）。
当截断日志的时候，这些VLF就可以被释放回收，逻辑日志的开头也会移动到最后一个被阶段VLF文件末端。
假设这个时候，在MinLSN位置后，发生了一个事务，一直没有commit，导致VLF3，VLF4，VLF5都被使用，那么就会重用之前回收的VLF文件。
正常情况下，如果经常截断旧的日志记录，保持逻辑日志的末端不到达逻辑日志的开头，满足下一个检查点之前船舰的所有新日志记录都有足够的空间存储，那么日志文件将永远不会被填满，保持一定的大小，可通过定期备份事务日志来达到。
但是如果，逻辑日志的结尾跟开头碰面了，那么当磁盘空间足够的情况下，则按照 自动增大大小 指定的数量 增大日志文件，并在 物理日志文件中添加多个VLF文件；如果磁盘空间不足，比指定的 增量大小 要少，那么就会报错，出现9002错误，导致数据库无法进行所有写操作。
5 延迟日志截断原因
日志截断会由于多种因素发生延迟。可查询sys.databases目录视图的 log_reuse_wait 和 log_reuse_wait_desc 列来发现是什么（如果有）阻止了日志的截断。 下表对这些列的值进行了说明。
6 管理事务日志
定期监控日志文件的大小跟实际使用大小，以防日志增长异常，占满磁盘空间，可通过以下两种方式查看 日志文件使用情况.
1 #查看日志使用情况，文件大小及实际使用大小 2 dbcc sqlperf(logspace) 3 4 #查看文件相关信息 5 select name,physical_name,size*8.0/1024 size_Mb,* from sys.database_files
定期日志备份，两个备份的间隔是运行丢失数据的时间跨度，不要过于频繁备份，会对数据库IO造成一定影响。
1 BACKUP LOG [backupdb] 2 TO DISK = N'D:\data\20170215_backupdb_log.trn' WITH NOFORMAT, NOINIT, NAME = N'backupdb-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10 3 GO
事务尽可能短，避免长时间开启事务，或者忘记commit/roll back；
解决事务日志已满问题（9002错误）
- 若是限制了文件最大值，在磁盘空间有剩余的情况下，增加日志文件的大小。
- 释放磁盘空间以便日志可以自动增长。
- 在其他磁盘上添加日志文件。
- 备份日志后，收缩日志。
- 将日志文件移到具有足够空间的磁盘驱动器。
参考文档：
如果转载，请注明博文来源： www.cnblogs.com/xinysu/ ，版权归 博客园 苏家小萝卜 所有。望各位支持！
1 创建数据文件时，在考虑什么
1.1 数据文件与文件组
数据文件有两类，一类是主数据文件，一类是辅助数据文件。
每一个数据库都有一个主数据文件数据文件用来存储数据，扩展名是 mdf。
一个数据库可以有0到多个的辅助数据文件，扩展名是 ndf。
文件组这个概念，可能大多数人只有在涉及表分区的时候有了解过。
顾名思义，文件组，就是给数据文件分为多个组，方便分配磁盘IO资源以及运维管理。每个数据库至少有一个文件组，含数据库主数据文件的组称之为 主文件组，一般不指定文件组名创建表格或者索引，则会默认把数据文件放在主文件组中，因为默认 主文件组就是 默认文件组，当然，也可以通过ALTER 语句来修改默认文件组为 其他文件组，这样，创建数据文件但不指定文件组时，则存放到设置的默认文件组中。
这里有个注意事项：数据库中的大多数操作都是仅针对于文件组操作，比如创建索引或者创建表格。
那么，什么情况下需要单文件，什么情况下有需要多个辅助数据文件呢？
- 建立表格及索引时，只能指定到某一个文件组，不能够指定到这个文件组的某个文件
- 同一个文件组内的数据文件，起到一个平摊分布数据的作用，如果是位于不同的驱动器，则有利于提高并发IO，如果是位于同一个驱动器，则有利于后期的运维管理；
- 当使用表分区的时候，每一个分区会使用到一个辅助数据文件（可以同一个驱动器，也可以不同）
- 大库的灵活运维管理，其实呢，如果在同一个驱动器上建立多个数据文件，对IO性能并没有任何改善，但是，却为后期的管理提供了方便性，尤其是大库管理，比如线上数据库损坏，需要还原出来一个新的数据文件，或者是测试环境的搭建等等，很多时候会遇到剩余的磁盘空间并不足以来存放这个大库，但是如果是多个数据文件，那么就可以分开指定驱动器存储，减少磁盘大小的要求。
那么，什么情况下，会使用到多个文件组呢？
- 使用表分区
- 当磁盘IO资源出现瓶颈的情况下，可以考虑迁移部分热表到 其他文件组的文件上（不同驱动器），分散IO；
- 当磁盘空间不足但是想把文件中的 冷表（类似与记录登录日志）的表格，迁移到其他驱动器上，可以考虑使用文件组；
- 历史数据和热数据分开，历史归档数据损坏，不影响热数据；
- 大库的灵活运维管理，可以使用文件组来备份数据库的一部分，比如某些特定的表格放在 辅助数据文件上，出事故后，还原的时候，可以对数据库进行部分还原，主文件组还原结束，即可提供服务，但在其他文件组上的对象暂时不能使用，等到其他文件组也还原结束，其存储的数据才能提供服务。
如何新增文件或者文件组呢？如何迁移表格数据到新的文件组呢？
1 --案例 1 ：给数据库 dbpage新增 文件组 testfg，并在这个文件组内建立辅助数据文件 dbpage_3，dbpage_4 2 USE [master] 3 GO 4 ALTER DATABASE [dbpage] ADD FILEGROUP [testfg] 5 GO 6 7 ALTER DATABASE [dbpage] 8 ADD FILE ( 9 NAME = N'dbpage_3', 10 FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\dbpage_3.ndf' , 11 SIZE = 51200KB , 12 FILEGROWTH = 10240KB 13 ) TO FILEGROUP [testfg] 14 GO 15 16 ALTER DATABASE [dbpage] 17 ADD FILE ( 18 NAME = N'dbpage_4', 19 FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\dbpage_4.ndf' , 20 SIZE = 51200KB , 21 FILEGROWTH = 10240KB 22 ) TO FILEGROUP [testfg] 23 GO 24 25 --案例 2 ：指定文件组创建表格 26 CREATE TABLE tbtest(id int not null,name varchar(10) not null) on [testfg] 27 28 --案例 3 ：迁移表到其他文件组 29 --表无聚集索引，通过建立聚集索引，把整个表格迁移到 指定文件组 30 alter table tbtest add constraint pk_tbtest primary key (id) on [testfg] 31 32 --表有聚集索引 33 方法一：重建聚集索引，先删除聚集索引，然后再建立新的聚集索引指定到文件组，如上一个SQL 34 方法二：利用表分区，先建立 中间表格，中间表添加分区方案，分区建立在 指定的文件组上，然后再 需要迁移到表格上执行 swith partion，然后重命名表格，最后删除旧表，中间表格的分区脚步这里不涉及 35 36 ALTER TABLE tbtest SWITCH PARTITION 1 TO tbtest_new PARTITION 1 37 GO 38 39 EXEC sp_rename 'tbtest','tbtest_old' 40 EXEC sp_rename 'tbtest_new','tbtest' 41 GO 42 43 DROP TABLE tbtest_old 44 GO
检查某个表格在文件组的分布
1 select
2
3 fg.name fgname,o.name tbname ,index_id,rows,au.type_desc,au.container_id,au.total_pages,au.used_pages,au.data_pages
4 from sys.partitions p
5 join sys.allocation_units au on p.partition_id=au.container_id
6 join sys.filegroups fg on fg.data_space_id=au.data_space_id
7 join sys.objects o on p.object_id=o.object_id
8 where o.type='u' and p.object_id=object_id('orders')
检查每个文件组一共有多少个表格
1 with data as(
2 select
3 fg.name fg_name, o.name tbname
4 from sys.partitions p
5 join sys.allocation_units au on p.partition_id=au.container_id
6 join sys.objects o on p.object_id=o.object_id
7 join sys.filegroups fg on fg.data_space_id=au.data_space_id
8 where o.type='u'
9 group by o.name,fg.name
10 )
11 select
12 a.fg_name,
13 count(*) tbcount,
14 tbnames=stuff((select ','+b.tbname from data b where a.fg_name=b.fg_name order by tbname for xml path('')),1,1,'')
15 from data a
16 group by fg_name
1.2 增长选项
设置数据库文件的似乎，需要判断是否启用自动增长，如果启用，是采用百分比增长还是按指定大小增长，是否设置文件最大大小。
首先，在线上业务，建议是：不设置文件的最大大小，避免 某些业务数据异常增长 导致空间不足，当然，这个设置的前提是，做了磁盘剩余空间监控及报警。
既然不设置最大文件大小，那么就需要设置自动增长，但是，注意，建议在数据库最开始的时候，就设置足够大的空间，避免频繁自动增长，每次自动增长都会在增长期间影响到数据库的IO性能，从而影响数据库的使用，所以建议在最开始的时候，设置足够大的空间，如果后面发现文件自动增长比较频繁，可以找一个业务低峰期，再扩大数据文件，设置文件增长大小，建议不要设置为 百分比，避免数据库太大，按百分比，一次增长太大，导致增长影响时间加长，监建议设置为 指定大小 ，可以在200M左右，实际可根据磁盘性能及增长情况来调整。
增长的调整，可以通过指定选项FILEGROWTH ，设置百分比 FILEGROWTH = 10%或者 设置指定大小 FILEGROWTH = 204800KB，或者通过界面操作。
1.3 即时初始化
说起文件增长，这里要提一个至关重要的 windows 系统参数配置：即时初始化（Instant File Initialization）。
什么是初始化呢？
当服务需要申请存储空间来使用 时，操作系统需要用零来填充空间，填充结束则完成初始化操作，但是，如果申请的空间比较大时，会耗费非常久的时间。
什么是即时初始化呢？
即时文件初始化功能将回收使用的磁盘空间，而无需使用零填充空间，直接跳过了零填充的过程，新数据写入文件时会覆盖磁盘内容。如果SQL SERVER服务登录用户开启了即时初始化，那么就可以瞬间完成对数据文件的初始化，注意，日志文件不能立即初始化。
SQL SERVER 中哪些操作可以即时初始化？
- 创建数据库
- 向现有数据库中添加文件
- 增大现有文件的大小、包括自动增长操作（不含日志文件的自动增长）
- 还原数据库或文件组
就拿建立数据库来说，不设置即时初始化文时，创建一个100G的数据库需要接近6min，但是开启了即时初始化后，仅需要3秒。更好的应用是在自动增长这块，能有效减少自动增长的时间，从而大大减少自动增长的影响时间。
如何开启即时初始化？
查看SQLSERVER引擎的登录用户->给该用户添加 ' 执行卷维护服务 ' -> 重启SQLSERVER服务。
查看SQL SERVER引擎的登录用户，如下：
打开 `管理工具`，点击 `本地安全策略` ，按下图找到 `执行卷维护任务` ，双击后选择添加 SQL server 服务的登录用户，然后点击 `应用` 即可。
添加后，需要重启SQL SERVER服务，使其加载该权限。所以，建议在数据库一开始安装的时候，就配置好该权限，或者在数据库宕机或者维护期间，做该操作。
如何检查是否开启即时初始化？
检查创建DB的时候，是否直接跳过零填充的过程。
1 /* 2 以全局方式打开跟踪标记 3004 和 3605。 3 3004：查看SQL Server对日志文件进行填零初始化的过程 4 3605：要求DBCC的输出放到SQL server ERROR LOG 5 -1：以全局方式打开指定的跟踪标记。 6 */ 7 8 DBCC TRACEON(3004,3605,-1) 9 GO 10 11 --创建测试库 12 CREATE DATABASE [xinysu] 13 CONTAINMENT = NONE 14 ON PRIMARY 15 ( NAME = N'xinysu', 16 FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\xinysu.mdf' , 17 SIZE = 104857600KB , FILEGROWTH = 204800KB 18 ) 19 LOG ON 20 ( NAME = N'xinysu_log', 21 FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER2012\MSSQL\DATA\xinysu_log.ldf' , 22 SIZE = 524288KB , FILEGROWTH = 102400KB 23 ) 24 GO 25 26 --查看错误日志 27 Exec xp_readerrorlog 0,1,Null,Null,'2017-05-29 10:28:00','2017-05-29 10:30:00' 28 29 --删除测试库 30 DROP DATABASE xinysu 31 GO 32 33 DBCC TRACEOFF(3004,3605,-1) 34 GO
可以看到，创建数据库xinysu，数据文件100G，日志文件512Mb，都是直接跳过零填充的过程，速度非常快。
2 DB收缩
2.1 指令及设置
执行收缩的指令有两种：shrinkfile跟shrinkdatabase。一个是指定某个文件进行压缩，一个是指定某个数据库，对数据库下的所有文件进行压缩。
Shrinkfile的指令如下：
1 DBCC SHRINKFILE ( { file_name | file_id } { [ , EMPTYFILE ] | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ] } ) [ WITH NO_INFOMSGS ]
2
3 /*
4 target_size
5 用兆字节表示的文件大小（用整数表示）。 如果未指定，则 DBCC SHRINKFILE 将文件大小减少到默认文件大小。 默认大小为创建文件时指定的大小。如果target_size指定，DBCC SHRINKFILE 尝试将文件收缩到指定的大小。 将要释放的文件部分中的已使用页重新定位到保留的文件部分中的可用空间。
6
7 EMPTYFILE
8 将所有数据从指定的文件都迁移到其他文件相同的文件组。 换而言之，清空文件将迁移数据，从指定的文件到同一个文件组中的其他文件。 清空文件可确保你没有新数据将添加到文件。可以通过删除该文件ALTER DATABASE语句。
9
10 NOTRUNCATE
11 文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。 因此，指定 NOTRUNCATE 时，文件看起来未收缩。
12 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。
13
14 TRUNCATEONLY
15 将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。
16 target_size如果使用 TRUNCATEONLY 指定将被忽略。
17 TRUNCATEONLY 选项不会移动日志中的信息，但会删除日志文件末尾的失效 VLF。
18
19 WITH NO_INFOMSGS
20 取消显示所有信息性消息。
21 */
22
23 --举例说明
24 DBCC SHRINKFILE ( dbpage_data, 100 )
25
26 DBCC SHRINKFILE ( dbpage_data, EMPTYFILE)
27 --清空 dbpage_data 数据文件上面的所有内容
28
29 DBCC SHRINKFILE ( dbpage_data, 100 ,NOTRUNCATE)
30 --收缩数据库 datapage的数据文件，文件名师 dbpage_data，收缩到100Mb
31 --重新分配超过100Mb的数据行到前面100Mb未分配的区，保留空闲空间
32
33 DBCC SHRINKFILE ( dbpage_data, TRUNCATEONLY)
34 --收缩数据库 datapage的数据文件，文件名是 dbpage_data，文件末尾未使用的空间释放给操作系统，不会重新分配数据行到未分配的区
35
Shrinkdatabase指令使用如下：
1 DBCC SHRINKDATABASE ( database_name | database_id | 0 [ , target_percent ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ) [ WITH NO_INFOMSGS ]
2
3 /*
4 database_name | database_id | 0
5 要收缩的数据库的名称或 ID。 如果指定 0，则使用当前数据库。
6
7 target_percent
8 数据库收缩后的数据库文件中所需的剩余可用空间百分比。
9
10 NOTRUNCATE
11 通过将已分配的区从文件末尾移动到文件前面的未分配区来压缩数据文件中的数据。 target_percent是可选的。
12 文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。 因此，指定 NOTRUNCATE 时，数据库看起来未收缩。
13 NOTRUNCATE 只适用于数据文件。 日志文件不受影响。
14
15 TRUNCATEONLY
16 将文件末尾的所有可用空间释放给操作系统，但不在文件内部执行任何页移动。 数据文件只收缩到最后分配的区。 target_percent如果使用 TRUNCATEONLY 指定将被忽略。
17 TRUNCATEONLY 将影响日志文件。 若要仅截断数据文件，请使用 DBCC SHRINKFILE。
18
19 WITH NO_INFOMSGS
20 取消严重级别从 0 到 10 的所有信息性消息。
21 */
22
23 --举例说明
24 DBCC SHRINKDATABASE (dbpage, 20)
25 --对数据库dbpage执行收缩处理，其中收缩后空闲空间占整个数据库大小的 20%
26 --等同于先执行 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE) ，再执行DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)
27
28 DBCC SHRINKDATABASE (dbpage, 20, NOTRUNCATE)
29 --对数据库dbpage执行收缩处理，其中收缩后空闲空间占整个数据库大小的 20%
30 --数据文件，分配文件末尾的区到文件前面未分配的区，压缩空间不会返回给操作系统，文件大小不变
31
32 DBCC SHRINKDATABASE (dbpage, 20, TRUNCATEONLY)
33 --对数据库dbpage执行收缩处理，但是收缩的空间不一定是 20%
34 --收缩的空间是文件末尾的可用空间，也就是 target_percent 在这里指定了也没有用
35 --日志文件跟数据文件，释放文件末尾的可用空间给系统文件，但是文件内不执行任何数据页移动
测试数据库 dbpage，先查看数据库的空间分布情况，再收缩数据库，使得收缩后的数据，剩余的空余空间占整个数据库的50%。
use dbpage
GO
sp_spaceused
--数据库总大小 58M，其中未使用的空间有 40Mb
dbcc shrinkdatabase ('dbpage',50)
--执行收缩后，返回数据文件占用2512个数据页，实际使用1248个数据页，预估可以再收缩1248个数据页；日志文件占用288个数据页。
sp_spaceused
收缩是指，回收数据库未使用的空间，如果数据文件20M，但是实际大小只有10M，那么DBCC SHRINKFILE 指定target_size=15M，则是把最后5Mb上面实际存储的数据内容重新分配到前面15Mb中未分配的区中，注意，DBCC SHRINKFILE 不会将文件收缩到小于存储文件中的数据所需要的大小。 例如，数据文件 实际使用 7 MB ，但占用 10 MB ，执行 DBCC SHRINKFILE 语句target_size的 6 时，将文件收缩到仅 7 MB，不是 6 MB。对于dbcc shrinkdatabase，也是跟以上的注意事项一样。
设置有自动收缩跟手动收缩两种。
自动收缩，可在 数据库 的`属性` 设置，把自动收缩设置为true，也可以执行命令如下：
USE [master]
GO
ALTER DATABASE [databasename] SET AUTO_SHRINK ON WITH NO_WAIT
GO
自动收缩，其运行结果等同于 执行 dbcc shrinkdatabase(dbname,25)，及在数据库中保留25%的自由空间，其他剩余空间回收，每30分钟检查一次来收缩数据。
2.2 原理
执行的时候，对数据库的每一个文件逐个进行压缩，从文件的末尾开始压缩。数据库引擎将按照 target_percent ，预估出每个文件可以压缩的空间，把文件末尾可压缩的空间上面的区，移动到前面不压缩的空闲区上。
比如 一个数据库 xinysu，有数据文件及日志文件各一个，其中数据文件有20Mb，实际使用12Mb，如果设置 target_percent 为40%，那么意味这压缩后的 数据文件的 理想大小 = 8Mb/( 1-0.4 ) = 13.3 Mb ，则理想的压缩结果 为 13.3 Mb 。则数据文件末尾的 20Mb-13.3Mb = 6.7Mb 空间上面有实际存储数据的 区，则会移动到 前面13.3Mb空间的空闲区上。
对于日志文件说来，数据库引擎会尝试收缩每个日志文件到目标大小，但是这里会有个前提，如果虚拟日志中的所有逻辑日志没有超过指定的目标大小，那么文件会正常截断，收缩到指定的目标大小，如果逻辑日志的大小大于指定的大小，那么数据库引擎将会尽可能多的收缩空间的空间，但是收缩情况不如理想状态。
执行收缩前，可以通过sp_spaceused或者 sys.database_files来查看空闲空间，再根据空闲空间来收缩。
SELECT
name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
也可以通过 dbcc showconfig来查看表格的空间分布情况
dbcc showcontig(tbtest)
/*
DBCC SHOWCONTIG 正在扫描 'tbtest' 表...
表: 'tbtest' (290100074)；索引 ID: 1，数据库 ID: 10
已执行 TABLE 级别的扫描。
- 扫描页数................................: 336367
- 扫描区数..............................: 42175
- 区切换次数..............................: 203346
- 每个区的平均页数........................: 8.0
- 扫描密度 [最佳计数:实际计数].......: 20.68% [42046:203347]
- 逻辑扫描碎片 ..................: 59.42%
- 区扫描碎片 ..................: 68.06%
- 每页的平均可用字节数.....................: 2485.3
- 平均页密度(满).....................: 69.29%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
某些情况下，会遇到，明明数据库文件是有空闲空间的，但是这些空闲的区，分散在每一个区中，而不是完整的有多个空闲的区，那么这个情况下，则无法有效的收缩数据库，因为 DBCC SHRINKFILE做的操作是 基于区操作的，它会把使用过的区前移，但是，它不会合并区合并页，如果数据库中，大多数区都只是使用少量数据页，那么收缩的效果也不会明显的。
收缩对于数据库来说，是一个耗费IO资源以及会增加碎片的操作，不宜过于频繁执行DB 收缩。
收缩可能会出现几个问题：
- 文件并没有变小
-
- 是否执行的命令含有 NOTRUNCATE
- 是否指定的大小比实际数据的大小还要小
- 数据文件没有空闲的区
- 日志文件中的LSN无法截断，详情查看本系列第6篇
- 执行时间非常久
-
- 某些 基于版本控制隔离级别 的事务 堵塞了 收缩操作，这里会在 errorlog中有记录，可以查看
-
- 如果是这个原因堵塞，可以选择停止收缩操作或者停止事务操作或者等待
- 回收的空间特别大，并且回收的空间上有大量的数据页面需要重分配到前面的空闲数据页面上
3 空间计算方法和区别
日志文件的空间统计，可以使用DBCC SQLPERF(LOGSPACE)指令，可以看到整个实例所有数据库的日志使用百分比及日志大小。
那么，数据文件呢？
数据库计算数据文件空间使用情况，有好几种方式，这些方式在统计的时候，注意区分是基于区统计的还是基于页面统计的以及执行代价。
3.1 基于区统计
DBCC SHOWFILESTATS 。
该指令基于区统计数据文件的使用情况，从GAM和SGAM页面读取对区的分配信息，计算整个数据文件中有多少区被分配。所以只需要读取数据文件中的GAM和SGAM页面内容既可以统计，方便快速，不会增加系统的额外负担。
注意，这里显示的是使用的区情况，所以，如果需要转换成kb，一个区8个数据页面，一个数据页面有8kb，文件名为 dbpage_3的文件大小为 800*8*8kb=51200kb=50Mb。
3.2 基于页面统计
3.2.1 sp_spaceused
exec sp_spaceused [objectname, updateusage]。
有两个可选参数，可以不选择也可以任意选择1-2个，objectname 默认为空， 如果不指定objectname，则是统计当前数据库的页面使用情况，如果指定，则是只统计某一个对象；updateusage 默认为 FALSE，如果指定 updateusage=TRUE，则在执行前，对当前数据库或者指定的 object 执行 DBCC UPDATEUSAGE with no_infomsgs，执行结束 UPDATEUSAGE后，再执行 sp_spaceused 。
DBCC UPDATEUSAGE with no_infomsgs 会对数据库做什么操作呢？
它会针对表或索引中的每个分区更正行、已用页、保留页、叶级页和数据页的计数， 如果系统表中没有错误，则 DBCC UPDATEUSAGE 不返回数据。 如果发现错误，并对其进行了更正，同时返回系统表中更新的行和列。由此可见，对于整个数据库或者某个大表执行 UPDATEUSAGE ，由于需要完整统计表格的页面使用情况，所以会耗费一定量的IO资源，对性能有一定影响。
所以，一般情况下，执行sp_spaceused来统计空间使用情况时，不建议设置 updateusage=TRUE，除非怀疑 sp_spaceused的输出结果有误。
sp_spaceused是如何来统计数据文件使用情况呢？
查看 系统存储过程 sp_spaceused 的SQL代码，可以看到无论是基于库统计还是基于表格统计，都是通过这三个 sys.dm_db_partition_stats，sys.internal_tables ，sys.partitions 动态管理视图来查看的。而这几张视图的数据并非是实时更新，所以，有时候，在对表格做索引删除或新增或者大量删除表格数据后，执行sp_spaceused可能不是很准确，这个时候，就需要设置选项 updateusage=TRUE。
测试结果如下：
其实，通过原理及测试情况，发现，sp_spaceused其实就是一个简易版的查询放到了存储过程中使用，但是由于其参数的限制，要不是分析整个数据库，要不是只能够分析某一个表格，无法批量分析，这个多多少少有些不方便，如果需要分析多个表格，则可以通过DMV视图来实现，详见下一小节。
3.2.2 sys.dm_db_partition_status
动态视图的查询，其实跟sp_spaceused不加updateusage选项的原理是一样的，不过这个查询可以动态调整，方便一些。
查询SQL如下，根据所需动态调整即可。
1 select
2 o.name,
3 sum(case when (p.index_id<2) then row_count end) rows,
4 sum(p.reserved_page_count)*8 reseved_kb,
5 sum(p.reserved_page_count-p.used_page_count)*8 unused_kb,
6 sum(p.used_page_count)*8 used_kb,
7 sum(case when (p.index_id<2) then (p.in_row_data_page_count+p.lob_used_page_count+p.row_overflow_used_page_count)
8 else p.lob_used_page_count+p.row_overflow_used_page_count end
9 )*8 data_kb,
10 sum(p.used_page_count-(case when (p.index_id<2) then (p.in_row_data_page_count+p.lob_used_page_count+p.row_overflow_used_page_count)
11 else p.lob_used_page_count+p.row_overflow_used_page_count end)
12 )*8 index_kb
13 from sys.dm_db_partition_stats p inner join sys.objects o on p.object_id=o.object_id
14 where o.type='u'
15 and o.name in ('orders','tba','tb_clu_no_unique')
16 group by o.name
17 order by o.name
3.2.3 DBCC SHOWCONTIG
这个指令在检查数据库碎片的时候，经常使用到。 用于显示指定的表或视图的数据和索引的碎片信息。这个指令在不指定FAST的情况下，可以说是最精确的统计方式了，细致到某个表格用了多少页，页面上的数据使用情况，碎片率如何，每次统计的时候，都会扫描这个表格的涉及到数据页，扫描过程会对逐个页面加锁然后释放，故在业务高峰期的时候，慎用，一方面是IO资源占用，另一方面是锁影响。
DBCC SHOWCONTIG
[ (
{ table_name | table_id | view_name | view_id }
[ , index_name | index_id ]
) ]
[ WITH
{
[ , [ ALL_INDEXES ] ]
[ , [ TABLERESULTS ] ]
[ , [ FAST ] ]
[ , [ ALL_LEVELS ] ]
[ NO_INFOMSGS ]
}
]
--语法说明
table_name,table_id,view_name,view_id
可以指定object_name检查，或者object_id来查看;如果不指定任意对象，则说明是检查当前整个数据库的所有表格
index_name,index_id
指定对象后，指定某个索引进行分析；如果不指定，则是按照聚集索引分析，没有聚集索引则是全表分析；
ALL_INDEXES
分析扫描所有索引，包含非聚集索引
TABLERESULTS
返回结果按照表格显示
FAST
指定是否要对索引执行快速扫描和输出最少信息。 快速扫描不读取索引的叶级或数据级页。
ALL_LEVELS
仅为保持向后兼容性而保留
NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。
测试聚集索引表格tbindex，含2个非聚集索引。测试情况如下：
DBCC SHOWCONTIG (tbindex)
DBCC SHOWCONTIG (tbindex,ix_number_name)
DBCC SHOWCONTIG (tbindex) WITH TABLERESULTS,ALL_INDEXES
DBCC SHOWCONTIG (tbindex) WITH TABLERESULTS,ALL_INDEXES,FAST
总体来说，如果是基于整个数据文件来看空间使用情况，DBCC SHOWFILESTATS是首选；如果需要动态查询表格的空间使用情况，可以使用DMV sys.dm_db_partition_status ；如果需要非常全面的分析表格的空间情况、碎片情况，则是用DBCC SHOWCONTIG。各个指令使用时，需明确其性能影响及准确性。
总结空间计算的指令，说明如下：
参考文档：
《SQL SERVER 2012实施与管理实战指南》第8章
