一、背景

      很久没关注线上的SQL Server数据库,今天突然接到电话,说我们的网站挂掉了,保存不了数据!!经过各种排查,还有各位小伙伴的帮助,发现数据库文件是保存在C盘的,C盘剩余空间为0,晕死,这还能保存数据就奇怪了。

二、SQL Server 恢复模式

      数据库文件有78G,日志文件3G多。看了一下数据库的恢复模式是Full(完整模式),难怪日志很大了。

数据库有三种恢复模式:简单恢复模式完整恢复模式大容量日志恢复模式
通常,数据库使用完整恢复模式或简单恢复模式。 数据库可以随时切换为其他恢复模式。

      如果我们不需要日志记录,需要删除3G多的日志文件,那么我们必须将数据库模式更改为:Simple(简单模式)

Simple(简单模式):无日志备份。自动回收日志空间以减少空间需求,实际上不再需要管理事务日志空间。
Full(完整模式):需要日志备份。数据文件丢失或损坏不会导致丢失工作。
可以恢复到任意时点(例如应用程序或用户错误之前)。
Bulk logged(大容量日志):需要日志备份。可以恢复到任何备份的结尾。 不支持时点恢复。
详细信息参考微软官网:恢复模式 (SQL Server)

      切换数据库模式的方法:选择数据库名称->点击鼠标右键->属性->打开的对话框中选择:选项->右边选择恢复模式->点击确定保存:
恢复模式图示.png
      修改恢复模式,也可以通过SQL语句来做,个人感觉没必要,想深入了解的可以自行百度。

三、通过收缩数据库和日志文件来达到减少硬盘空间的效果

      回到正题,接下来是收缩数据库的方法,分别是:
      ①使用SQL Server可视化界面
可视化操作收缩.png

      ②使用命令DBCC SHRINKDATABASE 与 DBCC SHRINKFILE(推荐)

-- 收缩数据库
DBCC SHRINKDATABASE(数据库名,20)

-- 收缩文件
DBCC SHRINKFILE (数据库逻辑名称, 1);
DBCC SHRINKFILE (数据库日志逻辑名称, 1);

-- SHRINKDATABASE与SHRINKFILE的区别:
-- 本质上SHRINKDATABASE循环调用SHRINKFILE的方法将数据库文件和日志文件都做收缩。
-- 以上选择一种方式执行即可
DBCC SHRINKDATABASE   
( database_name | database_id | 0   
     [ , target_percent ]   
     [ , { NOTRUNCATE | TRUNCATEONLY } ]   
)  
[ WITH NO_INFOMSGS ]
参数:
database_name | database_id | 0
要收缩的数据库名称或 ID。 0 指定使用当前数据库。
target_percent
数据库收缩后的数据库文件中所需的剩余可用空间百分比。
NOTRUNCATE
将分配的页面从文件的末尾移动到文件前面的未分配页面。 此操作会压缩文件中的数据。target_percent 是可选的。 Azure SQL 数据仓库不支持此选项。
文件末尾的可用空间不会返回给操作系统,并且文件的物理大小也不会更改。 因此,指定 NOTRUNCATE 时,数据库似乎不会收缩。
NOTRUNCATE 只适用于数据文件。 NOTRUNCATE 不影响日志文件。
TRUNCATEONLY
将文件末尾的所有可用空间释放给操作系统。 不移动文件内的任何页面。 数据文件仅收缩到最后指定的盘区。 如果使用 TRUNCATEONLY 指定,则会忽略 target_percent。 Azure SQL 数据仓库不支持此选项。
TRUNCATEONLY 将影响日志文件。 若要仅截断数据文件,请使用 DBCC SHRINKFILE。
WITH NO_INFOMSGS
取消严重级别从 0 到 10 的所有信息性消息。
DBCC SHRINKFILE   
(  
    { file_name | file_id }   
    { [ , EMPTYFILE ]   
    | [ [ , target_size ] [ , { NOTRUNCATE | TRUNCATEONLY } ] ]  
    }  
)  
[ WITH NO_INFOMSGS ]  
参数:
file_name
要收缩的文件的逻辑名称。
file_id
要收缩的文件的标识 (ID) 号。 若要获取文件 ID,请使用 FILE_IDEX 系统函数,或查询当前数据库中的 sys.database_files 目录视图。
target_size
整数,文件的新大小(以 MB 为单位)。 如果未指定,DBCC SHRINKFILE 缩小到文件创建大小。

四、附录:

      各种模式切换注意点:

1、SIMPLE->FULL 完成切换后,需要立刻进行差异备份,恢复日志链
2、FULL->SIMPLE:先进行日志备份,因为切换会中断日志链
3、BULK_LOGGED<->full 自由切换
4、simple<->BULK_LOGGED :不常见