sql server 的Maintenance Plans(维护计划)详解

下面说下我遇到的场景，就是我通过数据库自身的维护计划建立了数据库收缩自动计划，却发现数据库并没有实际性收缩。

前奏自动化配置流程

数据库--管理---维护计划--双击（维护计划向导）--下一步--名称：test----选择一项或多项维护--收缩数据库--下一步---数据库：选择（列表勾选）确定，设置当前数据库大小超过制定值时收缩数据库（s） ,收缩后保留的可用空间（这两个数值的设置策略参看下面）---一直到完成

1、后来仔细阅读了一下官网文件（https://docs.microsoft.com/zh-cn/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017）：

2、如果不是太理解可以参考下面一位大神的理解（https://www.cnblogs.com/lx823706/p/5194558.html）：

dbcc sqlperf(logspace)  --查看数据库日志空间使用情况

use xxxdb

exec sp_spaceused 　　--查看具体数据库的空间使用情况

select * from sys.sysfiles　　　　--查看具体数据库的逻辑文件名以及日志文件名

dbcc shrinkfile　　　　　　　　　　--收缩数据库中指定数据文件（或日志文件）的命令

dbcc shrinkdatabase　　　　　　　　--收缩数据库中所有数据文件和日志文件命令

transact-sql 参考：

dbcc sqlperf

提供有关所有数据库中的事务日志空间使用情况的统计信息。

语法

dbcc sqlperf ( logspace )

注释

事务日志积累每个数据库中数据的更改信息。由 dbcc sqlperf(logspace) 返回的信息可用于监视使用的空间大小，并指明何时对事务日志进行备份或截断。

结果集

下表描述结果集内的列。

列名    定义
database name    数据库名称，为该数据库显示日志统计信息。
log size (mb)    日志的可用空间的实际大小。该大小比当初为日志空间分配的要小，因为 microsoft® sql server™ 保留一小部分磁盘空间用于内部头信息。
log space used (%)    事务日志信息当前所占用的日志文件的百分比。
status    日志文件状态（总是包含 0）。

权限

dbcc sqlperf 对任何用户默认权限。

示例

下例显示当前安装的所有数据库的 logspace 信息。

dbcc sqlperf(logspace)
go
下面是结果集：

database name log size (mb) log space used (%) status      
------------- ------------- ------------------ ----------- 
pubs                1.99219            4.26471           0 
msdb                3.99219            17.0132           0 
tempdb              1.99219            1.64216           0 
model                   1.0            12.7953           0 
master              3.99219            14.3469           0 

sp_spaceused

显示行数、保留的磁盘空间以及当前数据库中的表所使用的磁盘空间，或显示由整个数据库保留和使用的磁盘空间。

语法

sp_spaceused [[@objname =] 'objname']
    [,[@updateusage =] 'updateusage']

参数

[@objname =] 'objname'

是为其请求空间使用信息（保留和已分配的空间）的表名。objname 的数据类型是 nvarchar(776)，默认设置为 null。

[@updateusage =] 'updateusage'

表示应在数据库内（未指定 objname 时）还是在特定的对象上（指定 objname 时）运行 dbcc updateusage。值可以是 true 或 false。updateusage 的数据类型是 varchar(5)，默认设置为 false。

返回代码值

0（成功）或 1（失败）

结果集

如果省略 objname，则返回两个结果集。

列名    数据类型    描述
database_name    varchar(18)    当前数据库的名称。
database_size    varchar(18)    当前数据库的大小。
unallocated space    varchar(18)    数据库的未分配空间。

列名    数据类型    描述
reserved    varchar(18)    保留的空间总量。
data    varchar(18)    数据使用的空间总量。
index_size    varchar(18)    索引使用的空间。
unused    varchar(18)    未用的空间量。

如果指定参数，则返回下面的结果集。

列名    数据类型    描述
name    nvarchar(20)    为其请求空间使用信息的表名。
rows    char(11)    objname 表中现有的行数。
reserved    varchar(18)    为 objname 表保留的空间总量。
data    varchar(18)    objname 表中的数据所使用的空间量。
index_size    varchar(18)    objname 表中的索引所使用的空间量。
unused    varchar(18)    objname 表中未用的空间量。

注释

sp_spaceused 计算数据和索引使用的磁盘空间量以及当前数据库中的表所使用的磁盘空间量。如果没有给定 objname，sp_spaceused 则报告整个当前数据库所使用的空间。

当指定 updateusage 时，microsoft® sql server™ 扫描数据库中的数据页，并就每个表使用的存储空间对 sysindexes 表作出任何必要的纠正。例如会出现这样一些情况：当除去索引后，表的 sysindexes 信息可能不是当前的。该进程在大表或数据库上可能要花一些时间运行。只有当怀疑所返回的值不正确，而且该进程对数据库中的其它用户或进程没有负面影响时，才应使用该进程。如果首选该进程，则可以单独运行 dbcc updateusage。

权限

执行权限默认授予 public 角色。

示例

a. 有关表的空间信息

下例报告为 titles 表分配（保留）的空间量、数据使用的空间量、索引使用的空间量以及由数据库对象保留的未用空间量。

use pubs
exec sp_spaceused 'titles'
b. 有关整个数据库的已更新空间信息

下例概括当前数据库使用的空间并使用可选参数 @updateusage。

use pubs
sp_spaceused @updateusage = 'true'

收缩数据库文件或日志文件命令：

dbcc shrinkfile

收缩相关数据库的指定数据文件或日志文件大小。

语法

dbcc shrinkfile
    ( { file_name | file_id }
        { [ , target_size ]
            | [ , { emptyfile | notruncate | truncateonly } ] 
        }
    )

参数

file_name

是已收缩文件的逻辑名称。文件名必须符合标识符的规则。有关更多信息，请参见使用标识符。

file_id

是要收缩的文件的标识 (id) 号。若要获得文件 id，请使用 file_id 函数或在当前数据库中搜索 sysfiles。

target_size

是用兆字节表示的所要的文件大小（用整数表示）。如果没有指定，dbcc shrinkfile 将文件大小减少到默认文件大小。

如果指定 target_size，dbcc shrinkfile 将试图将文件收缩到指定大小。将要释放的文件部分中的已使用页将重新定位到保留的文件部分中的可用空间。例如，如果数据文件为 10mb，则带有 target_size 为 8 的 dbcc shrinkfile 将导致文件最后 2 mb 中所有已用页重新分配到文件前 8 mb 中的任何可用槽中。dbcc shrinkfile 不会将文件收缩到小于存储文件中的数据所需要的大小。例如，如果使用 10mb 数据文件中的7 mb，带有 target_size 为 6 的 dbcc shrinkfile 语句只能将该文件收缩到 7 mb，而不能收缩到 6 mb。

emptyfile

将所有数据从指定文件中迁移到同一文件组中的其它文件。microsoft® sql server™ 不再允许将数据放在用于 emptyfile 选项的文件上。该选项允许使用 alter database 语句除去文件。

notruncate

导致将释放的文件空间保留在文件中。

当与 target_size 一起指定 notruncate 时，释放的空间不会释放给操作系统。dbcc shrinkfile 的唯一影响是将已使用的页从 target_size 行上面重新定位到文件的前面。当未指定 notruncate 时，所有释放的文件空间返回给操作系统。

truncateonly

导致文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次分配的大小，从而减少文件大小，而不移动任何数据。不尝试将行重新定位到未分配页。如果使用 truncateonly，将忽略 target_size。

注释

dbcc shrinkfile 适用于当前数据库中的文件。将上下文切换到数据库，以发出引用该特定数据库中文件的 dbcc shrinkfile 语句。有关更改当前数据库的更多信息，请参见 use。

所生成的数据库不能比 model 数据库更小。

使用 dbcc shrinkfile 以将文件减小到比其最初创建时更小。之后，文件的最小大小重置为新指定的大小。

若要删除在文件中可能出现的任何数据，请在执行alter database 之前执行 dbcc shrinkfile('file_name', emptyfile)。

要收缩的数据库不必在单用户模式下；收缩文件时，同时其他用户可以在数据库中工作。不必在单用户模式下运行 sql server 以对系统数据库进行收缩。

对于日志文件，sql server 使用 target_size 以计算整个日志的目标大小；因此，target_size 是收缩操作完成后日志中的可用空间大小。之后，整个日志的目标大小可以解释为每个日志文件的目标大小。dbcc shrinkfile 尝试立即将每个物理日志文件收缩至其目标大小。如果虚拟日志中的所有逻辑日志部分都没有超出日志文件的目标大小，该文件将成功截断，dbcc shrinkfile 完成且不显示任何消息。然而，如果虚拟日志中的逻辑日志部分超出目标大小，则 sql server 释放尽可能多的空间并发出一条消息。该信息告诉您需要执行什么操作来移动文件末尾超出虚拟日志的逻辑日志部分。执行完该操作后，可以重新发出 dbcc shrinkfile 命令以释放剩余的空间。有关收缩事务日志的更多信息，请参见收缩事务日志。

因为日志文件只能收缩到虚拟日志文件边界，所以不可能将日志文件收缩到比虚拟日志文件更小（即使现在没有使用该文件）。例如，可以将数据库的 1 gb 日志文件收缩到只有 128 mb。有关截断的更多信息，请参见截断事务日志。有关确定虚拟日志文件大小的更多信息，请参见虚拟日志文件。

结果集

下表描述结果集内的列。

列名    描述
dbid    sql server 试图收缩的文件的数据库标识号。
fileid    sql server 试图收缩的文件的文件标识号。
currentsize    文件当前占用的 8kb 页数。
minimumsize    文件可以占用的最小 8kb 页数。这与文件的最小大小或最初创建时的大小相对应。
usedpages    文件当前使用的 8kb 页数。
estimatedpages    sql server 估计文件能够收缩到的 8kb 页数。

权限

dbcc shrinkfile 权限默认授予 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员且不可转让。

示例

下例将 userdb 用户数据库中名为 datafil1 的文件收缩到 7 mb。

use userdb
go
dbcc shrinkfile (datafil1, 7)
go

收缩数据库中数据文件和日志文件的命令：

dbcc shrinkdatabase

收缩指定数据库中的数据文件大小。

语法

dbcc shrinkdatabase
    ( database_name [ , target_percent ]
        [ , { notruncate | truncateonly } ] 
    )

参数

database_name

是要收缩的数据库名称。数据库名称必须符合标识符的规则。有关更多信息，请参见使用标识符。

target_percent

是数据库收缩后的数据库文件中所要的剩余可用空间百分比。

notruncate

导致在数据库文件中保留所释放的文件空间。如果未指定，将所释放的文件空间释放给操作系统。

truncateonly

导致将数据文件中的任何未使用的空间释放给操作系统，并将文件收缩到上一次所分配的大小，从而减少文件大小，而不移动任何数据。不试图重新定位未分配页的行。使用 truncateonly 时，忽略 target_percentis。

注释

microsoft® sql server™ 可收缩：

特定数据库的所有数据和日志文件。执行 dbcc shrinkdatabase。

一次一个特定数据库中的数据或日志文件。执行 dbcc shrinkfile。
dbcc shrinkdatabase 以每个文件为单位对数据文件进行收缩。然而，dbcc shrinkdatabase 在对日志文件进行收缩时，看起来好像所有的日志文件都存在于一个连续的日志池中。

假设名为 mydb 的数据库有两个数据文件和两个日志文件。这些数据文件和日志文件大小都为 10 mb。第一个数据文件包含 6 mb 数据。

对于每个文件，sql server 计算目标大小，即要收缩文件到的大小。当用 target_percent 指定 dbcc shrinkdatabase 时，sql server 计算的目标大小是收缩后文件中的 target_percent 可用空间大小。例如，如果指定按 target_percent 为 25 收缩 mydb。sql server 计算的该文件的目标大小为 8 mb（6 mb 数据加上 2 mb 可用空间）。因此，sql server 将任何数据从数据文件的最后 2 mb 中移动到数据文件前 8 mb 的可用空间中，然后对该文件进行收缩。

假设 mydb 的第一个数据文件包含 7 mb 的数据。若将 target_percent 指定为 30，则允许将该数据文件收缩到所要的可用百分比 30。然而，若将 target_percent 指定为 40，就不会收缩该数据文件，因为 sql server 不能将文件收缩到比数据当前占有的空间还小的程度。也可以从另一个角度考虑这个问题：40% 要求的可用空间 + 70% 完整的数据文件（10 mb 中的 7 mb）大于 100%。因为所要求的可用百分比加上数据文件占用的当前百分比大于 100%（多出 10%），所以任何大于 30 的 target_size 都不会收缩该数据文件。

对于日志文件，sql server 使用 target_percent 以对整个日志的目标大小进行计算；因此，target_percent 是收缩操作后日志中的可用空间大小。之后，整个日志的目标大小可以解释为每个日志文件的目标大小。dbcc shrinkdatabase 尝试立即将每个物理日志文件收缩到其目标大小。如果虚拟日志中的所有逻辑日志部分都没有超出日志文件的目标大小，则该文件将成功截断，dbcc shrinkdatabase 完成且不显示任何消息。然而，如果虚拟日志中的逻辑日志部分超出其目标大小，则 sql server 释放尽可能多的空间并发出一条消息。该消息告诉您需要执行什么操作来移动文件末尾超出虚拟日志的逻辑日志部分。执行完该操作后，可以重新发出 dbcc shrinkdatabase 命令以释放剩余的空间。有关收缩事务日志的更多信息，请参见收缩事务日志。

因为日志文件只能收缩到虚拟日志文件边界，所以不可能将日志文件收缩到比虚拟日志文件更小（即使现在没有使用该文件）。例如，可以将数据库的 1 gb 日志文件收缩到只有 128 mb。有关截断的更多信息，请参见截断事务日志。有关确定虚拟日志文件大小的更多信息，请参见虚拟日志文件。

由 dbcc shrinkdatabase 计算的数据和日志文件的目标大小始终不小于文件的最小大小。文件的最小大小是在文件最初创建时指定的大小，或是上一次用更改文件大小的操作（如用带有 modify file 选项的 alter database 或 dbcc shrinkfile）显式设置的大小。例如，如果在执行 create database 时，将 mydb 的所有数据和日志文件大小都指定为 10 mb，则每个文件的最小大小都为 10 mb。dbcc shrinkdatabase 不能收缩任何小于 10 mb 的文件。如果使用带有 modify file 选项的 alter database 将其中的一个文件显示增长到 20 mb，则该文件新的最小大小为 20 mb。若要将文件大小收缩到小于其最小大小，请使用 dbcc shrinkfile 并指定新大小。执行 dbcc shrinkfile 会将文件的最小大小更改为所指定的新大小。

在使用数据文件时，dbcc shrinkdatabase 有 notruncate 和 truncateonly 选项。如果为日志文件指定这两个选项，则将忽略这两个选项。dbcc shrinkdatabase 不带任何选项等价于 dbcc shrinkdatabase 带有 notruncate 选项，后面还要跟着带有 truncateonly 选项的 dbcc shrinkdatabase。

不论是否指定 target_percent，notruncate 选项都执行 dbcc shrinkdatabase 的实际数据移动操作（包括将分配页从文件的末尾移动到文件前面的未分配页）。然而，文件末尾的可用空间不会返回给操作系统，文件的物理大小也不会更改。因此，如果指定 notruncate 选项，数据文件好像没有收缩。例如，假设再次使用 mydb 数据库，该 mydb 有两个数据文件和两个日志文件。第二个数据文件和第二个日志文件的大小都为 10 mb。执行 dbcc shrinkdatabase mydb notruncate 时，microsoft sql server 将数据从数据文件的后面几页移动到前面几页中。而文件的大小仍然为 10 mb。

truncateonly 选项将文件末尾的所有可用空间收回给操作系统。然而，truncateonly 在文件内不执行任何页移动操作。指定的文件只能收缩到上一次分配的大小。如果使用 truncateonly 选项指定 target_percent，则忽略后者。

所生成的数据库不能比 model 数据库更小。

要收缩的数据库不必在单用户模式下；其他的用户仍可以在数据库收缩时对其进行工作。这也包括系统数据库。

结果集

下表描述结果集内的列。

列名    描述
dbid    sql server 试图收缩的文件的数据库标识号。
fileid    sql server 试图收缩的文件的文件标识号。
currentsize    文件当前占用的 8kb 页数。
minimumsize    文件可以占用的最小 8kb 页数。这与文件的最小大小或最初创建时的大小相对应。
usedpages    文件当前使用的 8kb 页数。
estimatedpages    sql server 估计文件能够收缩到的 8kb 页数。

说明  sql server 并不显示未收缩的文件的行。

权限

dbcc shrinkdatabase 权限默认授予 sysadmin 固定服务器角色或 db_owner 固定数据库角色的成员且不可转让。

示例

下例将 userdb 用户数据库中的文件减小，以使 userdb 中的文件有 10% 的可用空间。

dbcc shrinkdatabase (userdb, 10)
go

sp_lock 进程号，打开资源管理器查看cpu使用状况。

tab 非临时表的x锁的数量变化（过多，持续不减少 ，有问题，需要停止收缩），要是产生死锁要及时停止收缩。

具体收缩步骤如下：

最后将文件末尾的所有可用空间释放给操作系统（dbcc shrinkfile（'xxdb', xx,truncateonly））:

 

上述实例是收缩的数据文件，若是收缩日志文件，则需以下操作：

首先更改数据库的用户恢复模式为简单模式，之后进行收缩

--数据库名称为 test  
use test  
   go  
   alter database test set recovery simple with no_wait  
   go  
   alter database test set recovery simple     
   go  
   use test   
   go  
--这里的2根据 select * from sysfiles 来查询 一般数据位1 日志为2  

   dbcc shrinkfile (2 , xx, truncateonly)  
--xx 为数据库文件收缩到的大小
   go  
   use test  
   go  
   alter database test set recovery full with no_wait  
   go  
   alter database test set recovery full    
   go  

 此外（针对于不需要记录日志数据库，也即是不需要恢复实时数据的库，可以通过如下方式来减少日志文件的增加，配合日志文件收缩效果更佳）：

backup log xxxdb to disk='nul' --nul并非null 的错误拼写， nul可以被看成一个虚拟文件，所有写入到 nul文件里的数据都被丢弃，对 sql server而言，nul 与其他真实存在的文件一样， sql server会扫描所有活动日志，将该日志格式化后写入 nul文件，数据写入到nul文件后被操作系统丢弃，然后由操作系统返回确认信息给 sql server，然后将活动日志标记为已日志备份，该活动日志所在空间可被 reuse。 --backup log xxxdb  to disk='nul' 相当于正常备份日志然后删除日志文件，并不破坏备份日志链。

如果你是蜗牛，那你就不必害怕自己前进的缓慢，相信你自己，因为你的脚步永远不会落空，只要你一步步的向上爬，金字塔也必定被你踩在脚下。