简介面向仍在维护 SQL Server 2000 的数据库管理员与开发人员这份文档针对企业管理器“收缩数据库”效果不明显、删除更新后冗余空间难以彻底释放的问题整理了一套借助 DBCC 命令深度压缩数据库文件的参考材料。压缩包内共 1 个 docx 文件约 256KB内容短小集中便于在旧库维护与空间排查时快速查阅。文档围绕数据库整体收缩、数据文件与日志文件收缩、文件使用情况更新等环节展开对压缩前后尺寸变化与空间释放效果作了直观说明并提示操作前先备份、留意 I/O 性能变化及频繁收缩带来的文件碎片风险兼顾空间回收与运行稳定。已有 311 人学习适合具备基础 T-SQL 与数据库维护经验、需要处理 MDF/LDF 体积偏大或优化旧版数据库存储占用的读者参考也可作为相关教学与培训中的案例材料。1. 从 2GB 的 .mdf 缩不回去说起这套 DBCC 手法解决什么某公司一台跑了六七年的业务库还挂在 Sqlserver2000 上.mdf 长到 2GB去年清掉一批历史流水之后磁盘占用纹丝不动。企业管理器里点“收缩数据库”进度条走完只掉了三十几 MB回头查 sysfiles 里的 size 还是老值。这套用 dbcc 命令做深度压缩数据库文件的做法干的就是把 delete 之后散落在文件内部的空页真正挤出去让文件长度退回到实际数据量附近。适合的人很明确手上还有 SQL Server 2000 实例在跑、磁盘余量告急、近期做过大批量 delete 或 drop table 的维护人员。不适合的也明确数据天天暴涨的库以及没做过完整备份的库。后一条不是客套收缩不可回滚翻车了只能靠备份。2. 企业管理器缩不动的原因8KB 页、区与两条 DBCC 命令的分工2.1 delete 之后文件为什么不缩页、区与文件尾截断SQL Server 2000 的最小存储单位是页一页 8KB。八个连续页组成一个区extent64KB。表和索引的数据就落在一页一页里页按区分配区按文件顺序往后长。这里要理解一个动作delete 语句删除一行做的事情是把行所在的位置标记成可复用页本身不会从文件里消失页所属的区也仍然算在这个对象头上。只有当一个区里所有页都空了SQL Server 才可能把这个区标记为未分配。但即便标记了未分配文件在磁盘上的长度也不会变——空间变成“文件内部的空闲空间”等着下次 insert 复用。文件收缩干的事情是从文件末尾往回看找到最后一个还装着数据的页把文件长度截到它后面。这就带来一个很直接的结论——文件尾部全是空页一截就掉一大块文件尾部还压着一张老表的一页数据中间空出几万页也截不动。这就解释了为什么删掉八成数据、文件一个字节没小。删掉的页散落在整个文件里文件末尾那几十页恰好还有活数据。企业管理器的“收缩数据库”跑完 CurrentSize 往往只退一点点根因就在这里。想让它退得更多得先把页从文件尾部往前挪或者把对象整体重建让数据在文件前部紧凑地排在一起。2.2 企业管理器的收缩选项少在哪NOTRUNCATE 与 TRUNCATEONLY企业管理器那个“收缩数据库”对话框底下调的就是 DBCC SHRINKDATABASE界面只给了“收缩后保留多少百分比空闲空间”一个输入框其余参数全取默认。命令行版本多两个选项这是图形界面给不了的选项行为什么时候用默认不带选项先把文件尾部已分配的页往前面空闲区挪再截断尾部每挪一轮都写日志想让文件真正变小NOTRUNCATE只挪页、不截断释放的空间留在文件内部想减少碎片又不想改文件大小TRUNCATEONLY只截尾部空闲区、不挪页速度最快确认尾部本来就是空的赶时间图形界面的默认行为相当于“带 target_percent 的默认模式”会挪页但只跑一轮就返回。第一轮挪完文件前部又空出位置它不会接着再挪第二轮——这是效果不明显的第二个原因。第三个原因是 target_percent 的默认值。它表示收缩后文件里保留多少百分比的空闲空间很多人看到对话框里填着 10 就以为只留 10%实际执行时如果库从没手工指定过行为会比预期保守。手工敲命令的价值在于把 target_percent、NOTRUNCATE、TRUNCATEONLY 拆开用先 TRUNCATEONLY 试一刀快能掉多少掉多少再用默认模式把剩下的页往前挪最后再 TRUNCATEONLY 收尾。这套组合拳在文件尾部被“钉子页”卡住的时候特别管用。2.3 SHRINKDATABASE 与 SHRINKFILE 的分工两条命令一个管全库一个管单文件。-- 全库收缩target_percent 表示收缩后保留的空闲空间百分比 DBCC SHRINKDATABASE (库名, 10) -- 只截文件尾部的空闲区不移动数据页速度最快 DBCC SHRINKDATABASE (库名, TRUNCATEONLY)SHRINKDATABASE 作用到库里所有数据文件和日志文件。日志文件的收缩不受 target_percent 约束日志是按 VLF虚拟日志文件为单位截断的能截多少取决于里面有没有活动事务。-- 单文件收缩第一个参数是 fileid 或逻辑文件名第二个参数目标大小单位是 MB DBCC SHRINKFILE (1, 0) -- 0 表示缩到创建时的初始大小 DBCC SHRINKFILE (库名_Data, 800) -- 也可以直接缩到 800MBtarget_size 的单位是 MB注意不是页这点跟 sysfiles.size 的页单位不一样换算的时候最容易错。不传 target_size 就缩到最小可能。两者的差别不只是粒度。SHRINKDATABASE 内部会遍历所有文件逐个算能挪多少SHRINKFILE 只处理一个文件返回结果里的 CurrentSize 就是你最关心的那个数字。我的习惯是先用 SHRINKDATABASE 打一遍把能自动处理的处理掉再用 SHRINKFILE 针对数据文件和日志文件逐个精修——日志文件尤其如此SHRINKDATABASE 对它的控制力很弱必须单独用 SHRINKFILE。2.4 先看清库和文件的家底任何收缩动作之前先把下面这段跑一遍USE 库名 GO SELECT fileid, -- 1主数据文件2第一个日志文件 name, -- 逻辑名SHRINKFILE 也能直接用它 filename, -- 物理路径 size, -- 当前大小单位 8KB 页 size * 8 / 1024 AS size_mb, -- 换算成 MB便于和磁盘对照 maxsize, -- -1 表示可增长到磁盘满 growth, -- 增长步长 status FROM sysfiles GO这段查询是后面每一步的依据fileid 决定 SHRINKFILE 第一个参数填几size 是收缩前后的对照基准maxsize 和 growth 反映这个库的增长策略有没有配失控。生产库里 growth 设成按百分比、maxsize 又设成 -1 的组合日志能在半夜悄悄把盘吃满这个后面还会提到。还有一点值得先说清SHRINKDATABASE 和 SHRINKFILE 都是物理收缩只改文件长度不改逻辑结构。表还是那张表索引还是那个索引页在文件里的相对顺序变了数据一条没少。所以收缩本身不会丢数据真正的风险出在过程中断和收缩后的副作用上。3. 摸清家底sysfiles 三个字段与 sp_spaceused 的交叉验证3.1 fileid、size、maxsize 各填什么sysfiles 每行对应一个物理文件一个库至少两行一行数据文件一行日志文件。fileid文件标识。1 是主数据文件2 是第一个日志文件。库如果按文件组拆到不同盘上fileid 会往后排到 3、4。SHRINKFILE 第一个参数填的就是它。name逻辑文件名也能直接当 SHRINKFILE 的参数用比记数字靠谱。filename物理路径收缩前最好确认它在哪块盘上别缩完才发现盘不对。size当前文件大小单位 8KB 页。100000 大约是 781MB。这是判断“缩没缩动”的唯一硬指标别拿资源管理器里的数字当准。maxsize最大能长到多大。-1 表示不限一直长到磁盘满。growth增长步长。0 表示文件大小固定、不自动增长生产库一般不会这么设。status位标志。0x4 位为 1 表示这是日志文件0x100000 位为 1 表示 growth 以百分比计。位标志读起来不直观用 CASE 翻译一下SELECT fileid, name, size * 8 / 1024 AS size_mb, CASE WHEN status 0x4 0x4 THEN 日志 ELSE 数据 END AS 文件类型, CASE WHEN status 0x100000 0x100000 THEN 按百分比增长 ELSE 按 MB 增长 END AS 增长方式, growth FROM sysfiles按百分比增长是个隐形坑growth 填 10 表示每次涨 10%一个 2GB 的库一次自动增长就是 200MB日志这么涨几次就把盘吃光而且在增长的那一瞬间写入会被阻塞。生产库一般改成固定 MB100MB 或 256MB 都行具体看单次事务的写入量。3.2 sp_spaceused 看库里到底用了多少sysfiles 的 size 是文件物理长度sp_spaceused 给的是库内对象的逻辑占用。两个数一比就知道有多少是“文件内部的空闲空间”。USE 库名 GO EXEC sp_spaceused GO输出几列含义和用法如下列名含义怎么用database_size库总大小含日志和磁盘占用对得上unallocated space尚未分配给任何对象的空间这部分收缩能直接拿掉reserved已保留给对象的总空间数据加索引加未用data数据实际占用—index_size索引占用—unused已保留但对象没用上的空间要靠整理索引才能释放看的是 reserved 和 data 的差值。reserved 远大于 data说明内部碎片多SHRINKDATABASE 默认模式把页往前挪的效果会明显reserved 本来就接近 data再缩也缩不出多少数据是真的只是删得不够多。逐表看更直观-- 遍历每张表输出占用找出删得最多、占得最大的表 EXEC sp_msforeachtable EXEC sp_spaceused ?sp_msforeachtable 是未公开存储过程SQL Server 2000 自带不需要额外安装。输出会很长一般先扫前几张大表。哪张表 reserved 和 data 差得离谱那张表就是重点处理对象。3.3 sysindexes 估算对象占用sysindexes 里每个索引一行indid 为 0、1、255 的三行分别对应堆、聚集索引、文本图像列。SELECT OBJECT_NAME(id) AS 对象名, indid, reserved * 8 / 1024 AS reserved_mb, used * 8 / 1024 AS used_mb, dpages AS 数据页数 FROM sysindexes WHERE indid IN (0, 1, 255) ORDER BY reserved DESCreserved 和 used 的单位都是 8KB 页。reserved 减 used 就是分给了对象但没用上的空间跟 sp_spaceused 的 unused 是一回事只是拆到了对象粒度。这一步能判断一件事空间是被一张大表占着还是被几十张小表均匀占着。前一种情况重建那张表的聚集索引可能比收缩更有效后一种情况老实走收缩流程。这个判断直接影响后面操作顺序别跳过。3.4 日志文件单独看-- 看每个库的日志大小和使用率 DBCC SQLPERF(LOGSPACE)输出 Database Name、Log Size (MB)、Log Space Used (%)、Status 四列。日志使用率长期在 90% 以上说明日志从来没被截断过这不是 SHRINKFILE 能解决的得先动恢复模式和备份策略。3.5 收缩前必须记下来的三个数动手前写下三行数据文件当前 size页数乘 8 除 1024 换算成 MB数据文件里 reserved 与 data 的差值日志文件当前 size以及 DBCC SQLPERF 给的使用率没有这三个数收缩完没法判断有没有效果也解释不了“为什么只掉 30MB”。这三个数还是出问题时的第一手证据事后拿来说服同事、写变更记录都靠它。4. 分步实操从备份到 SHRINKFILE 的完整命令序列4.1 先备份再确认恢复模式顺序不能反。收缩过程如果中断回滚要靠日志日志如果没备份又刚好被截断那就只能从上次的完整备份恢复。-- 全库备份WITH INIT 表示覆盖同名文件 BACKUP DATABASE 库名 TO DISK D:\backup\库名_20240101.bak WITH INIT GO -- 确认恢复模式和日志能否截断 SELECT DATABASEPROPERTYEX(库名, Recovery) AS 恢复模式 GO恢复模式是 SIMPLE日志会自动截断SHRINKFILE 缩日志一般能缩到位。恢复模式是 FULL 或 BULK_LOGGED日志只有做过日志备份才会被标记为可截断光靠收缩没用。SQL Server 2000 里有一种应急写法在完整恢复模式下临时截断日志-- 仅 SQL Server 2000 可用后续版本已移除该语法 BACKUP LOG 库名 WITH TRUNCATE_ONLY GO这行命令不产生可用的日志备份属于“后悔药”级别只在磁盘真要满了又来不及做正式日志备份时用。用完记得补一次完整备份否则时间点恢复能力就断了。顺便把自动收缩关掉避免手工缩完又被后台任务乱动USE master GO EXEC sp_dboption 库名, autoshrink, false GO4.2 SHRINKDATABASE 打第一轮-- 10 表示收缩后保留 10% 的空闲空间留一点余量比缩到 0 更稳 DBCC SHRINKDATABASE (库名, 10) GO返回结果长这样每行一个文件列含义DbId数据库 IDFileId文件 ID对应 sysfiles.fileidCurrentSize当前大小单位 8KB 页MinimumSize理论能缩到的最小值页UsedPages当前实际使用的页数EstimatedPages估算收缩后能到的页数重点看 CurrentSize 和 EstimatedPages 的差距。两者接近说明这一轮已经缩到头了差距很大说明还有页没挪动得靠 SHRINKFILE 精修或者重建索引。Command(s) completed successfully 这句提示不代表文件真的小了永远以 CurrentSize 和事后查 sysfiles.size 为准。4.3 SHRINKFILE 精修先缩数据还是先缩日志网上流传的步骤是先执行dbcc shrinkfile(2, 0)再执行dbcc shrinkfile(1, 0)也就是先日志后数据。我的习惯反过来先数据后日志理由很实在收缩数据文件本身要写日志你先把日志缩到最小紧接着缩数据文件又把日志撑起来等于白缩一遍。USE 库名 GO -- 第一步数据文件缩到初始大小 DBCC SHRINKFILE (1, 0) GO -- 第二步日志文件缩到初始大小 DBCC SHRINKFILE (2, 0) GOfileid 别照抄用第 3 章查出来的值。库里有多个数据文件时每个都得单独缩一遍漏一个文件等于没缩干净。如果 0 这个目标太激进可以改成具体 MB 数。判断依据是 sp_spaceused 里 data 加 index_size 的总和再乘 1.2 左右留余量-- 假设实际占用 1.2GB缩到 1.5GB 留出增长空间 DBCC SHRINKFILE (1, 1500) GO收缩完必须用同一段查询复核SELECT fileid, name, size * 8 / 1024 AS size_mb FROM sysfiles GO前后两个 size_mb 一对比掉没掉、掉了多少清清楚楚。这一步别省凭感觉判断收缩效果是最容易翻车的地方。4.4 收缩期间的锁与时间窗口SHRINKFILE 在挪页的时候会对受影响的区加锁业务写入会被阻塞。一个 2GB 的库收缩跑十几分钟到半小时都算正常期间日志还会同步增长。放在业务高峰做轻则超时重则应用报错。选时间窗口然后先看一眼当前连接EXEC sp_who2 GO看 HostName 和 ProgramName 两列确认没有业务程序连着。真要独占SQL Server 2000 的写法是USE master GO EXEC sp_dboption 库名, single user, true GO -- 收缩命令放在这里执行 EXEC sp_dboption 库名, single user, false GO改成单用户之后任何新连接都会被拒绝包括你自己的另一个查询窗口。执行顺序别搞错否则会把自己锁在外面。5. 收缩后的排查清单五类“空间没下来”的现象与处置5.1 现象一命令返回成功size 一点没变原因文件尾部有“钉子页”也就是最后一个已分配的区还压着数据。收缩只能从尾部往回截尾部不动前面空多少都没用。另一种可能是页挪动失败——文件前部找不到足够大的连续空闲区容纳尾部那些页。解决先用 TRUNCATEONLY 试一刀看能截掉多少DBCC SHRINKFILE (1, TRUNCATEONLY) GO截不动就说明尾部确实有活数据。这时候的重心不是继续缩而是把尾部那几张表的数据挪走重建它们的聚集索引或者把表迁到新文件组。重建索引的写法DBCC DBREINDEX (表名, , 90) GO第三个参数 90 是填充因子留 10% 空间给后续插入避免重建完立刻又页分裂。5.2 现象二日志文件缩完几分钟后又涨回去原因完整恢复模式下日志没做备份截断或者存在长事务、未提交事务把日志头钉住日志里的 VLF 一个都标不了可重用SHRINKFILE 只能缩到活动日志的边界活动日志有多大就只能缩到多大。解决先查有没有长事务DBCC OPENTRAN (库名) GO有输出就说明有事务从某个时间点一直开着找到对应连接处理掉。没有输出再看恢复模式完整恢复模式补一次日志备份。应急可以用 4.1 节的BACKUP LOG 库名 WITH TRUNCATE_ONLY但这是临时手段不是日常方案。治本的办法是把频繁写入的库切到简单恢复模式或者建立固定节奏的日志备份。日志文件反复涨缩本身对性能没好处文件增长那一瞬间所有写入都要等。5.3 现象三执行时报“无法获得对数据库的排他访问”或直接超时原因还有业务连接挂着或者有复制、作业、链接服务器在定时访问。收缩需要短暂独占只要有一个连接不放手就进行不下去。解决先用 sp_who2 看连接来源能停的应用先停。停不掉的走单用户模式SQL Server 2000 里用 sp_dboption 而不是 ALTER DATABASEUSE master GO EXEC sp_dboption 库名, single user, true GO USE 库名 GO DBCC SHRINKFILE (1, 0) GO USE master GO EXEC sp_dboption 库名, single user, false GO改回多用户这一步千万别忘忘了第二天上班整个系统连不上这就是典型的血泪经验。5.4 现象四缩完能跑但几个关键查询明显变慢原因收缩过程中页被重新搬动聚集索引的物理顺序和逻辑顺序对不上碎片率上升。SQL Server 2000 的预读是按区来读的碎片一多同样的数据要多读好几个区。解决收缩完对核心表做一次碎片检查再决定要不要整理-- 看表的碎片情况 DBCC SHOWCONTIG (表名) WITH ALL_INDEXES GO -- 碎片轻微用 INDEXDEFRAG在线整理 DBCC INDEXDEFRAG (库名, 表名, 1) GOScan Density 低于 75%、Extent Switches 明显偏大就该整理。大表直接 DBREINDEX 更快但会锁表得挑窗口。5.5 现象五收缩跑到一半失败数据库变成可疑状态原因磁盘在收缩过程中被写满或者实例异常重启。收缩是大事务中途失败要回滚回滚又需要日志空间空间不够就回滚不了库直接进 suspect。解决这种只能从备份恢复没有第二条路。所以第 4 章第一步就是备份不是走流程。收缩前额外确认目标盘剩余空间大于当前库大小的 1.5 倍日志盘单独再留一份。收缩过程中盯着磁盘别一边缩一边有别的作业往同一个盘写数据。6. 收尾一步与后续复核updateusage 和碎片该不该管6.1 更新使用情况收缩完成后系统表里记录的空间统计还停留在收缩前的数字sp_spaceused 可能给出偏大的结果。更新一下USE 库名 GO DBCC UPDATEUSAGE (0) GO参数传 0 表示在当前数据库上下文执行也可以直接传库名DBCC UPDATEUSAGE (库名)后者更明确不容易因为切换错库而更新错对象。这条命令会扫描表和索引的页分配库大的时候要跑几分钟别在业务时间做。6.2 复核清单收缩不是执行完就结束按下面三项过一遍再收工复核项命令通过标准文件大小SELECT size*8/1024 FROM sysfiles数据文件接近实际数据量日志使用率DBCC SQLPERF(LOGSPACE)低于 60%且不再持续上涨核心表碎片DBCC SHOWCONTIG(表名) WITH ALL_INDEXESScan Density 大于 75%三项里任何一项不通过说明这次收缩只是做了一半后面还欠一次索引整理或者日志策略调整。6.3 什么时候值得缩什么时候别动频繁收缩不是好习惯。文件缩下去业务一写又涨回来涨缩之间页在文件里反复搬家碎片只会越来越重。一个粗略的判断单次释放空间超过文件总大小 30%值得做每次只掉几十 MB不如把精力放在归档策略和索引维护上。从那以后我每次碰这类老库都强制走一遍“备份、记三个数、SHRINKDATABASE 打底、SHRINKFILE 精修、updateusage 收尾、碎片的复核”这个顺序少一步都不踏实。希望帮到你。本文还有配套的精品资源点击获取