简介面向仍在使用 SQL Server 2000 的数据库管理员与开发人员这份专题文档针对数据删除或更新后空间未充分释放、企业管理器“收缩数据库”效果有限的问题提供数据库文件深度压缩与空间回收的参考思路。包内共 1 个 docx 文件压缩包约 256KB内容集中便于查阅和归档。文档围绕 DBCC SHRINKDATABASE、DBCC SHRINKFILE 与 DBCC UPDATEUSAGE 展开涉及数据文件和日志文件的收缩处理、文件 ID 的识别、执行前备份提醒以及收缩后可能出现的页面重分布、I/O 性能变化和文件碎片等注意事项帮助读者理解深度压缩的适用场景与风险边界。已有 313 人学习适合旧版本数据库维护、容量排查和空间治理场景下的技术人员参考。1. 磁盘只剩 2GB 时我为什么不再点收缩数据库接手一台还在跑 SQL Server 2000 的老业务库MDF 已经涨到 9GB实际数据不到 3GB——中间那 6GB 是历史流水表被 DELETE 之后留下的空洞。企业管理器里右键所有任务 → 收缩数据库跑了三遍文件大小纹丝不动日志文件还在继续涨。问题不在工具坏而在那个入口只按百分比给一个目标值既不管日志有没有截断也搬不动文件尾部的活动页。后来改在查询分析器里按 sysfiles → SHRINKDATABASE → SHRINKFILE → UPDATEUSAGE 的顺序走一遍同一个库压到 2.6GB。下面把这套动作拆开sysfiles 每个字段怎么读、四条 DBCC 什么顺序不能反、参数 0 到底是什么含义、收缩完为什么查询会突然变慢。适合手上只有企业管理器和查询分析器、被磁盘告警追着跑的运维和二次开发同行。2. 读懂 sysfilesfileid、groupid 与 8KB 页的换算动手之前先把文件台账看清楚。SQL Server 2000 里每个数据库都有一张 sysfiles 系统表记录本库所有数据文件和日志文件的物理属性收缩命令的第一个参数就是从这里取的。很多人直接背1 是数据文件、2 是日志文件这在单文件库上碰巧对一旦库里有多个数据文件或者日志文件被重建过fileid 的顺序就会变照背就会缩错对象——把业务数据文件当日志缩或者反过来日志一动不动。2.1 sysfiles 里每个字段到底在说什么先把字段含义对一遍后面所有命令的参数都从这里推出来字段实际含义收缩时怎么用fileid文件在库内的编号从 1 开始SHRINKFILE 的第一个参数groupid0 表示日志文件1 表示主文件组其他值为用户文件组判断数据/日志比 fileid 可靠size文件当前大小单位是 8KB 页不是 MB乘以 8 除以 1024 才是 MBmaxsize0 表示不允许增长-1 表示可增长到磁盘满其他值为页数上限收缩后顺手核对避免又涨回去growth增长量含义取决于 status配 FILEGROWTH 时参考status位标志0x100000 位为 1 表示 growth 是百分比否则是页数判断增长配置是否合理name逻辑文件名SHRINKFILE 也可以用名字而不是 IDfilename物理路径含 .mdf / .ldf对照磁盘剩余空间最需要记住的一条groupid 0 的一定是日志文件groupid 1 的一定是主数据文件。fileid 只保证库内唯一不保证顺序所以脚本里判断文件类型要用 groupid不要用 fileid 的奇偶或者大小去猜。2.2 一条查询把页数换算成 MB并确认哪个是日志文件在查询分析器里先切到目标库再查 sysfiles。直接SELECT *出来一屏数字没法用size 那列是页数看着2048其实是 16MB。把换算和类型判断一起做进查询-- 在查询分析器里先选中目标数据库或用 USE 切过去 USE 你的库名 GO SELECT fileid, groupid, name, filename, size * 8 / 1024 AS size_mb, -- size 单位是 8KB 页换算成 MB CASE WHEN groupid 0 THEN 日志文件 ELSE 数据文件 END AS file_type, CASE WHEN maxsize -1 THEN 不限涨到磁盘满 WHEN maxsize 0 THEN 固定大小不增长 ELSE CAST(maxsize * 8 / 1024 AS VARCHAR(20)) MB END AS max_size_desc, CASE WHEN status 0x100000 0x100000 THEN CAST(growth AS VARCHAR(20)) % -- 百分比增长 ELSE CAST(growth * 8 / 1024 AS VARCHAR(20)) MB -- 按页增长 END AS growth_desc FROM sysfiles ORDER BY groupid, fileid这段查询解决三个问题一是把页数换成 MB和资源管理器里看到的文件大小对得上二是用 groupid 明确标出哪个是日志、哪个是数据后面写 SHRINKFILE 时不再靠猜三是把 maxsize 和 growth 一起打出来顺手判断这个库是不是每次涨一点点、涨一次卡一次的配置。等价命令还有EXEC sp_helpfile输出更省事但没有换算脚本里用不上。跑完记下两件事日志文件的 fileid 是多少、数据文件的 fileid 是多少。如果库里有多个数据文件只缩最大的那个不要一次全缩——多文件库的页分布是设计过的全缩会把文件组内的数据打散。再看一眼空间账确认文件多大和数据多少之间的差-- 当前库的数据/日志占用与剩余空间 EXEC sp_spaceused GO -- 每个文件的实际使用页数部分环境可能不支持报语法错误就跳过 DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES GOsp_spaceused 给出的是 reserved / data / index_size / unused 四个值。真正能压下去的量大致等于 unused 加上日志里非活动部分。如果 unused 很小而文件很大说明空间被索引页或者未提交事务的日志占着这时候硬缩只会白跑一遍。2.3 收缩前必须落地的三件事备份、恢复模式、自动收缩DBCC SHRINK 系列是不可逆操作页一旦前移原来的物理布局就没了。所以在查询分析器里敲第一条命令之前有三件事必须做完。第一件是备份而且要做完整备份而不是差异-- 完整备份到磁盘路径换成实际备份盘 BACKUP DATABASE 你的库名 TO DISK D:\backup\你的库名_before_shrink.bak WITH INIT, STATS 10 GO -- 备份完立刻验证别等出事才发现文件是坏的 RESTORE VERIFYONLY FROM DISK D:\backup\你的库名_before_shrink.bak GO第二件是确认恢复模式。日志文件能不能缩取决于日志有没有被截断FULL 模式下日志会一直留着等你备份不备份就永远缩不动-- 查看当前恢复模式SIMPLE / BULK_LOGGED / FULL SELECT DATABASEPROPERTYEX(你的库名, Recovery) AS recovery_model GO -- 如果业务允许临时切到简单模式日志会自动截断 ALTER DATABASE 你的库名 SET RECOVERY SIMPLE GO第三件是关掉自动收缩。这个选项在 SQL Server 2000 里默认可能是开的表现是后台线程周期性检查文件大小并自动缩业务高峰时跟正常读写抢 IO典型的玄学卡顿来源-- 关闭自动收缩两种写法等价老环境用 sp_dboption 更稳 EXEC sp_dboption 你的库名, autoshrink, FALSE -- 或 ALTER DATABASE 你的库名 SET AUTO_SHRINK OFF GO三件事做完再进下一步。时间窗也要挑收缩过程中会大量移动页大库跑起来是小时级的放在业务低峰并且提前把数据库设为单用户或者限制连接避免收缩到一半有人插事务。3. 四步 DBCC 链路从 SHRINKDATABASE 到 UPDATEUSAGE命令本身只有四行难点在于顺序和参数。顺序反了日志没截断就缩文件结果是跑了半小时文件没变参数写错第二个参数填 0 以为是缩到 0MB实际含义是缩到文件创建时的初始大小。这一章按执行顺序拆。3.1 DBCC SHRINKDATABASE 的两个参数和它的天花板语法是DBCC SHRINKDATABASE (database_name [, target_percent] [, {NOTRUNCATE | TRUNCATEONLY}])。第一个参数是库名第二个是收缩后文件内保留的可用空间百分比不指定时按系统默认值常见为 10%实际操作里建议显式写别依赖默认。-- 收缩整个库收缩后每个文件内保留 10% 可用空间 DBCC SHRINKDATABASE (你的库名, 10) WITH NO_INFOMSGS GONOTRUNCATE 和 TRUNCATEONLY 是两个互斥选项含义经常被搞混NOTRUNCATE 表示把已用页往文件前部搬但释放出来的空间不还给操作系统文件大小不变只是内部腾空TRUNCATEONLY 表示把文件末尾的空闲区直接还给操作系统但不搬页所以文件尾部只要有活动页就停在那儿。日常压缩用默认行为不写这两个选项即可即搬页 归还。这个命令有个硬天花板任何文件都不会被缩到小于创建时的初始大小而且只要文件末尾还有正在使用的页收缩就停在那一页前面的空洞再大也过不去。这正是 DELETE 掉中间表之后文件压不下去的根本原因——那些页是空闲了但文件最后几页还有活动数据收缩无法跨过去。输出里的 CurrentSize、MinimumSize、UsedPages、EstimatedPages 四列就是用来判断卡在哪EstimatedPages 明显小于 CurrentSize说明还有压缩余地两者接近说明已经到底。3.2 日志先截断再收缩SHRINKFILE 的顺序不能反日志文件的收缩单独处理而且必须先截断再缩。SQL Server 的日志是环形使用的活动部分未提交事务、未备份的日志记录所在的位置决定了能截到哪如果活动部分正好在日志文件末尾不截断直接缩结果就是几乎没动。-- 第一步截断日志。没有日志备份需求时用 TRUNCATE_ONLY -- 注意这会破坏日志备份链之后必须做一次完整备份才能恢复日志备份 BACKUP LOG 你的库名 WITH TRUNCATE_ONLY GO -- 老版本兼容写法效果相同 -- DUMP TRANSACTION 你的库名 WITH NO_LOG -- GO -- 第二步把日志文件收缩到指定大小单位 MB不要填 0 或 1 -- 第一个参数用 2.2 节查到的日志文件 fileid DBCC SHRINKFILE (2, 256) WITH NO_INFOMSGS GO日志的目标大小不要写 0。0 在 SHRINKFILE 里的含义是收缩到文件创建时的初始大小如果当初建库时日志初始大小就是 1MB会缩成一个极小的日志文件下一次大事务立刻又涨回去还可能因为增长次数太多产生大量日志碎片。合理的做法是按业务峰值事务量给一个固定值256MB 到 1GB 之间具体看每天最大的批量操作有多大。数据文件同理用查到的主数据文件 fileid-- 收缩主数据文件到初始大小0 表示创建时的初始大小不是 0MB DBCC SHRINKFILE (1, 0) WITH NO_INFOMSGS GO -- 如果 0 缩过头可以给一个明确的 MB 值比初始值大一些 -- DBCC SHRINKFILE (1, 2048) -- GO这里有个常见误解很多人以为第二个参数写 0 就是缩到最小。实际含义是缩到文件创建时的初始大小这个值可能远大于实际数据量也可能小于你想要的结果。想看真实能缩到多少先跑一次 SHRINKDATABASE 看 EstimatedPages再决定填 0 还是填具体 MB。3.3 DBCC UPDATEUSAGE 修的是统计口径不是空间前三步跑完文件大小已经变了但系统表里记录的页数、行数可能还是旧的。大量 DELETE 之后sysindexes 里的页计数会虚高导致 sp_spaceused 显示的空间和实际不符下次再收缩时估算的目标也是错的。UPDATEUSAGE 干的就是重算这些计数。-- 0 表示当前数据库也可以写库名 DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS GO -- 只想修某张表时可以精确到表名 -- DBCC UPDATEUSAGE (0, 流水表) -- GO它不释放任何磁盘空间跑完文件大小不会变但下一次收缩的估算会准sp_spaceused 的数字也终于能对上。大库上这个命令可能跑很久因为要扫所有表和索引的页分配信息放窗口期执行如果只是个别表删得狠精确到表名能省不少时间。3.4 串成一份可回放的脚本把四步写成一份带变量的脚本下次换库只改变量。注意每步之间不要跳尤其日志截断必须在日志收缩之前SET NOCOUNT ON DECLARE dbname sysname DECLARE logfile int DECLARE datafile int DECLARE logmb int SET dbname 你的库名 SET logmb 256 -- 日志目标大小按业务峰值定 -- 1. 取文件 ID SELECT logfile fileid FROM sysfiles WHERE groupid 0 SELECT datafile fileid FROM sysfiles WHERE groupid 1 AND fileid (SELECT MIN(fileid) FROM sysfiles WHERE groupid 1) PRINT 日志文件 ID CAST(logfile AS VARCHAR(10)) PRINT 数据文件 ID CAST(datafile AS VARCHAR(10)) -- 2. 整库收缩保留 10% 可用空间 DBCC SHRINKDATABASE (dbname, 10) WITH NO_INFOMSGS -- 3. 截断日志后单独缩日志文件 BACKUP LOG 你的库名 WITH TRUNCATE_ONLY DBCC SHRINKFILE (logfile, logmb) WITH NO_INFOMSGS -- 4. 缩数据文件到初始大小 DBCC SHRINKFILE (datafile, 0) WITH NO_INFOMSGS -- 5. 修正统计口径 DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS -- 6. 看结果 EXEC sp_spaceused SELECT fileid, groupid, name, size * 8 / 1024 AS size_mb FROM sysfiles GO脚本里的 logmb 是唯一需要按环境调的参数日报类业务 256MB 足够有 nightly 批量导入的给 1GB。datafile 取主文件组里最小的 fileid多文件库请改成按 size 最大来选。4. 避坑五次翻车记录与现场处置这套命令我在不同环境上跑过很多次真正出问题的从来不是命令写错而是对状态的判断错。下面五条按现象 → 原因 → 解决记都是当场处置过的。4.1 收缩完几乎没变小日志没截断现象DBCC SHRINKFILE 跑完日志文件从 4GB 变成 3.9GB几乎没动重复跑也没用。原因日志的活动部分在文件末尾未截断的情况下 SQL Server 不能越过活动部分收缩。FULL 恢复模式下如果长期没做日志备份日志会一直保留缩多少都是白费。解决先执行BACKUP LOG 库名 WITH TRUNCATE_ONLY或临时切 SIMPLE 模式并做一次 CHECKPOINT再缩。缩完记得补一次完整备份因为 TRUNCATE_ONLY 已经破坏了日志链之前的日志备份无法再用于时间点恢复。这条是血泪经验别省。4.2 fileid 猜错把数据文件缩到初始大小现象本来只想缩日志执行DBCC SHRINKFILE (1, 0)之后日志没变数据文件从 6GB 掉到 800MB业务报表直接报空间不足。原因这台机器的库里 fileid 1 是主数据文件日志是 2和1 数据 2 日志的口诀正好对上但另一次环境里因为文件被重建过顺序反了。靠猜必翻车。解决回到 2.2 节的查询用 groupid 判断类型脚本里把 fileid 查出来赋给变量不要手写常量。已经缩错了的只能靠备份恢复或者让文件重新增长——但增长回来的空间分布是新的索引碎片会很严重要重建索引。4.3 收缩后查询变慢页碎片和索引碎片现象磁盘腾出来了第二天开始有人反馈某张表的查询从 1 秒变成 8 秒执行计划没变。原因收缩是把页从文件尾部往前搬搬的过程中页的顺序被打乱物理读从顺序扫描退化成随机读同时索引页的填充度被打散扫描密度下降。解决收缩完必须做一次索引维护。先DBCC SHOWCONTIG看扫描密度和页密度再决定用DBCC INDEXDEFRAG在线、慢、不重建还是DBCC DBREINDEX离线、快、彻底。经验值扫描密度低于 90% 就重建大表放维护窗口。4.4 自动收缩开着业务高峰被拖住现象每天上午十点左右系统整体变慢CPU 不高磁盘队列很长没有明显慢查询。原因数据库开了 autoshrink后台收缩线程在高峰时检测到文件有空闲就启动移动页的同时和业务读写抢 IO。收缩越频繁碎片越多下次更慢形成循环。解决EXEC sp_dboption 库名,autoshrink,FALSE关掉改成定期维护窗口手工收缩并把文件的 growth 设成固定 MB 而不是百分比减少增长次数。关掉之后 IO 抖动通常当天就消失。4.5 没备份就下手回滚无门现象收缩中途查询分析器连接断开再连上发现数据库处于恢复状态业务停了两个小时。原因收缩大库耗时长期间有人重启了实例或者磁盘写满事务回滚加上日志恢复把时间拉长更糟的是没做备份出问题只能等。解决操作前必做完整备份并 RESTORE VERIFYONLY 验证大库收缩期间把库设为单用户或限制连接确认备份盘和数据库盘不是同一块物理盘。这套流程后来我做成检查清单缺一项就不动手。5. 收缩之后的善后碎片、增长参数与验收收缩只解决文件多大不解决用起来快不快。真正决定这次操作值不值的是收完之后的三件事碎片有没有清、增长参数有没有调、验收数字有没有留档。5.1 用 SHOWCONTIG 看碎片用 DBREINDEX 重建先量化碎片别凭感觉重建。SHOWCONTIG 关注两个指标扫描密度Scan Density越接近 100% 越好逻辑扫描碎片Logical Scan Fragmentation越低越好。-- 看单张表的碎片情况 DBCC SHOWCONTIG (流水表) WITH ALL_INDEXES, TABLERESULTS GO -- 重建整表所有索引填充因子 90留出插入空间 DBCC DBREINDEX (流水表, , 90) GO -- 大表不想锁太久用索引整理代替重建在线碎片清理不彻底 -- DBCC INDEXDEFRAG (0, 流水表, 1) -- GODBREINDEX 会重建索引页顺带把数据页也重新组织效果最彻底但需要额外空间大约等于该表索引大小并且在 SQL Server 2000 上是阻塞的必须放维护窗口。INDEXDEFRAG 是在线操作业务可以继续跑但只做页内整理碎片清不干净适合白天做小修补。重建完成后再跑一次 SHOWCONTIG 对比扫描密度从 70% 回到 99% 是常见结果。这一步不做第 4.3 条的变慢几乎是必然的。5.2 增长参数怎么改才不反复收缩完最怕的是文件很快又涨回去涨回去的过程就是碎片产生的过程。把 growth 从百分比改成固定 MB是成本最低的改善对象建议配置理由数据文件 growth固定 256MB 或 512MB百分比增长在大库上单次增长过大造成写入尖峰数据文件 maxsize按磁盘规划设上限不要用 -1避免某个库把磁盘吃光拖垮实例日志文件 growth固定 128MB 或 256MB小步增长会产生大量日志碎片日志文件 maxsize留出 2 倍峰值事务量太小会频繁增长太大可能撑爆磁盘autoshrink关闭后台收缩与业务抢 IO-- 修改数据文件的增长方式SQL Server 2000 支持 MODIFY FILE ALTER DATABASE 你的库名 MODIFY FILE ( NAME 你的逻辑文件名, FILEGROWTH 256MB, MAXSIZE 20480MB ) GO -- 日志文件同理逻辑名从 sysfiles 的 name 列取 ALTER DATABASE 你的库名 MODIFY FILE ( NAME 你的日志逻辑名, FILEGROWTH 128MB, MAXSIZE 8192MB ) GO改完用EXEC sp_helpfile复核确认 growth 列显示的是 MB 而不是百分比。5.3 验收三张数字对比收缩前后操作做完别急着收工留一份前后对比既验证效果也给下次操作留基线。至少要对比三组数sysfiles 里的文件大小、sp_spaceused 的逻辑占用、SHOWCONTIG 的碎片率。指标收缩前收缩后判断标准数据文件 sizeMB记录实测值记录实测值下降幅度应接近 unused 的量日志文件 sizeMB记录实测值记录实测值应接近设定的目标值sp_spaceused 的 unused记录实测值记录实测值收缩后应显著下降扫描密度记录实测值重建后记录应回到 95% 以上逻辑扫描碎片记录实测值重建后记录应低于 5%对比表里如果文件变小了但 unused 没降说明收缩只是搬了页没释放空间回去检查是不是用了 NOTRUNCATE如果unused 降了但扫描密度还是很低回去补 DBREINDEX。两个都对上这次操作才算收干净。6. 批量体检脚本一次看清所有库能压出多少单库处理熟了之后真正费时间的是到底哪个库值得缩。一台实例上几十个库逐个点开看 sysfiles 不现实。写个游标扫一遍 sysdatabases把所有库的文件大小、类型、增长方式打出来按大小排序先缩最大的那个。-- 在 master 库执行扫描实例上所有用户数据库的文件情况 SET NOCOUNT ON DECLARE db sysname DECLARE sql nvarchar(2000) CREATE TABLE #files ( dbname sysname, fileid int, groupid int, logical sysname, size_mb int, growth_mb int ) DECLARE cur CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE dbid 4 -- 排除 master/model/msdb/tempdb AND status 512 0 -- 排除正在恢复的库 OPEN cur FETCH NEXT FROM cur INTO db WHILE FETCH_STATUS 0 BEGIN SET sql INSERT INTO #files SELECT db , fileid, groupid, name, size * 8 / 1024, CASE WHEN status 0x100000 0x100000 THEN -1 ELSE growth * 8 / 1024 END FROM [ db ].dbo.sysfiles EXEC (sql) FETCH NEXT FROM cur INTO db END CLOSE cur DEALLOCATE cur -- 按库汇总一眼看出谁最占地方 SELECT dbname, SUM(CASE WHEN groupid 0 THEN size_mb ELSE 0 END) AS log_mb, SUM(CASE WHEN groupid 0 THEN size_mb ELSE 0 END) AS data_mb, SUM(size_mb) AS total_mb, MAX(CASE WHEN growth_mb -1 THEN 1 ELSE 0 END) AS has_pct_growth FROM #files GROUP BY dbname ORDER BY total_mb DESC DROP TABLE #files GO脚本里status 512用来过滤正在恢复的库growth_mb -1标记出还在用百分比增长的库这些库优先改配置。跑出来的 total_mb 排序就是处理顺序一次缩一个缩完做索引重建别图快连着缩好几个——IO 顶不住的时候整台实例都会跟着卡。还有一种情况这个脚本会暴露出来某个库 data_mb 不大但 log_mb 特别大。这种库八成是 FULL 恢复模式长期没做日志备份先查恢复模式和备份作业别急着缩缩了还会涨回去。有一个边界得说清楚如果某个库缩完两三天又涨回原样说明它的空间是被真实业务写回来的不是空洞这时候再缩就是纯粹的折腾。合适的做法是评估归档周期把历史数据迁走或者用新建库 BCP/DTS 导入的方式做一次物理重整比反复收缩干净得多。从那以后我每次动手前都会先把 2.2 节那条 sysfiles 查询跑一遍、把备份验证跑一遍缺一步就不敲 SHRINKFILE。数据库这东西快是次要的能回退才是第一位的。希望帮到你。本文还有配套的精品资源点击获取 
长河君 · 2026/11/4 21:10:36 · 阅读 1.2k SQL Server 2000数据库收缩不生效：sysfiles定位与DBCC四步避坑
简介面向仍在使用 SQL Server 2000 的数据库管理员与开发人员这份专题文档针对数据删除或更新后空间未充分释放、企业管理器“收缩数据库”效果有限的问题提供数据库文件深度压缩与空间回收的参考思路。包内共 1 个 docx 文件压缩包约 256KB内容集中便于查阅和归档。文档围绕 DBCC SHRINKDATABASE、DBCC SHRINKFILE 与 DBCC UPDATEUSAGE 展开涉及数据文件和日志文件的收缩处理、文件 ID 的识别、执行前备份提醒以及收缩后可能出现的页面重分布、I/O 性能变化和文件碎片等注意事项帮助读者理解深度压缩的适用场景与风险边界。已有 313 人学习适合旧版本数据库维护、容量排查和空间治理场景下的技术人员参考。1. 磁盘只剩 2GB 时我为什么不再点收缩数据库接手一台还在跑 SQL Server 2000 的老业务库MDF 已经涨到 9GB实际数据不到 3GB——中间那 6GB 是历史流水表被 DELETE 之后留下的空洞。企业管理器里右键所有任务 → 收缩数据库跑了三遍文件大小纹丝不动日志文件还在继续涨。问题不在工具坏而在那个入口只按百分比给一个目标值既不管日志有没有截断也搬不动文件尾部的活动页。后来改在查询分析器里按 sysfiles → SHRINKDATABASE → SHRINKFILE → UPDATEUSAGE 的顺序走一遍同一个库压到 2.6GB。下面把这套动作拆开sysfiles 每个字段怎么读、四条 DBCC 什么顺序不能反、参数 0 到底是什么含义、收缩完为什么查询会突然变慢。适合手上只有企业管理器和查询分析器、被磁盘告警追着跑的运维和二次开发同行。2. 读懂 sysfilesfileid、groupid 与 8KB 页的换算动手之前先把文件台账看清楚。SQL Server 2000 里每个数据库都有一张 sysfiles 系统表记录本库所有数据文件和日志文件的物理属性收缩命令的第一个参数就是从这里取的。很多人直接背1 是数据文件、2 是日志文件这在单文件库上碰巧对一旦库里有多个数据文件或者日志文件被重建过fileid 的顺序就会变照背就会缩错对象——把业务数据文件当日志缩或者反过来日志一动不动。2.1 sysfiles 里每个字段到底在说什么先把字段含义对一遍后面所有命令的参数都从这里推出来字段实际含义收缩时怎么用fileid文件在库内的编号从 1 开始SHRINKFILE 的第一个参数groupid0 表示日志文件1 表示主文件组其他值为用户文件组判断数据/日志比 fileid 可靠size文件当前大小单位是 8KB 页不是 MB乘以 8 除以 1024 才是 MBmaxsize0 表示不允许增长-1 表示可增长到磁盘满其他值为页数上限收缩后顺手核对避免又涨回去growth增长量含义取决于 status配 FILEGROWTH 时参考status位标志0x100000 位为 1 表示 growth 是百分比否则是页数判断增长配置是否合理name逻辑文件名SHRINKFILE 也可以用名字而不是 IDfilename物理路径含 .mdf / .ldf对照磁盘剩余空间最需要记住的一条groupid 0 的一定是日志文件groupid 1 的一定是主数据文件。fileid 只保证库内唯一不保证顺序所以脚本里判断文件类型要用 groupid不要用 fileid 的奇偶或者大小去猜。2.2 一条查询把页数换算成 MB并确认哪个是日志文件在查询分析器里先切到目标库再查 sysfiles。直接SELECT *出来一屏数字没法用size 那列是页数看着2048其实是 16MB。把换算和类型判断一起做进查询-- 在查询分析器里先选中目标数据库或用 USE 切过去 USE 你的库名 GO SELECT fileid, groupid, name, filename, size * 8 / 1024 AS size_mb, -- size 单位是 8KB 页换算成 MB CASE WHEN groupid 0 THEN 日志文件 ELSE 数据文件 END AS file_type, CASE WHEN maxsize -1 THEN 不限涨到磁盘满 WHEN maxsize 0 THEN 固定大小不增长 ELSE CAST(maxsize * 8 / 1024 AS VARCHAR(20)) MB END AS max_size_desc, CASE WHEN status 0x100000 0x100000 THEN CAST(growth AS VARCHAR(20)) % -- 百分比增长 ELSE CAST(growth * 8 / 1024 AS VARCHAR(20)) MB -- 按页增长 END AS growth_desc FROM sysfiles ORDER BY groupid, fileid这段查询解决三个问题一是把页数换成 MB和资源管理器里看到的文件大小对得上二是用 groupid 明确标出哪个是日志、哪个是数据后面写 SHRINKFILE 时不再靠猜三是把 maxsize 和 growth 一起打出来顺手判断这个库是不是每次涨一点点、涨一次卡一次的配置。等价命令还有EXEC sp_helpfile输出更省事但没有换算脚本里用不上。跑完记下两件事日志文件的 fileid 是多少、数据文件的 fileid 是多少。如果库里有多个数据文件只缩最大的那个不要一次全缩——多文件库的页分布是设计过的全缩会把文件组内的数据打散。再看一眼空间账确认文件多大和数据多少之间的差-- 当前库的数据/日志占用与剩余空间 EXEC sp_spaceused GO -- 每个文件的实际使用页数部分环境可能不支持报语法错误就跳过 DBCC SHOWCONTIG WITH TABLERESULTS, ALL_INDEXES GOsp_spaceused 给出的是 reserved / data / index_size / unused 四个值。真正能压下去的量大致等于 unused 加上日志里非活动部分。如果 unused 很小而文件很大说明空间被索引页或者未提交事务的日志占着这时候硬缩只会白跑一遍。2.3 收缩前必须落地的三件事备份、恢复模式、自动收缩DBCC SHRINK 系列是不可逆操作页一旦前移原来的物理布局就没了。所以在查询分析器里敲第一条命令之前有三件事必须做完。第一件是备份而且要做完整备份而不是差异-- 完整备份到磁盘路径换成实际备份盘 BACKUP DATABASE 你的库名 TO DISK D:\backup\你的库名_before_shrink.bak WITH INIT, STATS 10 GO -- 备份完立刻验证别等出事才发现文件是坏的 RESTORE VERIFYONLY FROM DISK D:\backup\你的库名_before_shrink.bak GO第二件是确认恢复模式。日志文件能不能缩取决于日志有没有被截断FULL 模式下日志会一直留着等你备份不备份就永远缩不动-- 查看当前恢复模式SIMPLE / BULK_LOGGED / FULL SELECT DATABASEPROPERTYEX(你的库名, Recovery) AS recovery_model GO -- 如果业务允许临时切到简单模式日志会自动截断 ALTER DATABASE 你的库名 SET RECOVERY SIMPLE GO第三件是关掉自动收缩。这个选项在 SQL Server 2000 里默认可能是开的表现是后台线程周期性检查文件大小并自动缩业务高峰时跟正常读写抢 IO典型的玄学卡顿来源-- 关闭自动收缩两种写法等价老环境用 sp_dboption 更稳 EXEC sp_dboption 你的库名, autoshrink, FALSE -- 或 ALTER DATABASE 你的库名 SET AUTO_SHRINK OFF GO三件事做完再进下一步。时间窗也要挑收缩过程中会大量移动页大库跑起来是小时级的放在业务低峰并且提前把数据库设为单用户或者限制连接避免收缩到一半有人插事务。3. 四步 DBCC 链路从 SHRINKDATABASE 到 UPDATEUSAGE命令本身只有四行难点在于顺序和参数。顺序反了日志没截断就缩文件结果是跑了半小时文件没变参数写错第二个参数填 0 以为是缩到 0MB实际含义是缩到文件创建时的初始大小。这一章按执行顺序拆。3.1 DBCC SHRINKDATABASE 的两个参数和它的天花板语法是DBCC SHRINKDATABASE (database_name [, target_percent] [, {NOTRUNCATE | TRUNCATEONLY}])。第一个参数是库名第二个是收缩后文件内保留的可用空间百分比不指定时按系统默认值常见为 10%实际操作里建议显式写别依赖默认。-- 收缩整个库收缩后每个文件内保留 10% 可用空间 DBCC SHRINKDATABASE (你的库名, 10) WITH NO_INFOMSGS GONOTRUNCATE 和 TRUNCATEONLY 是两个互斥选项含义经常被搞混NOTRUNCATE 表示把已用页往文件前部搬但释放出来的空间不还给操作系统文件大小不变只是内部腾空TRUNCATEONLY 表示把文件末尾的空闲区直接还给操作系统但不搬页所以文件尾部只要有活动页就停在那儿。日常压缩用默认行为不写这两个选项即可即搬页 归还。这个命令有个硬天花板任何文件都不会被缩到小于创建时的初始大小而且只要文件末尾还有正在使用的页收缩就停在那一页前面的空洞再大也过不去。这正是 DELETE 掉中间表之后文件压不下去的根本原因——那些页是空闲了但文件最后几页还有活动数据收缩无法跨过去。输出里的 CurrentSize、MinimumSize、UsedPages、EstimatedPages 四列就是用来判断卡在哪EstimatedPages 明显小于 CurrentSize说明还有压缩余地两者接近说明已经到底。3.2 日志先截断再收缩SHRINKFILE 的顺序不能反日志文件的收缩单独处理而且必须先截断再缩。SQL Server 的日志是环形使用的活动部分未提交事务、未备份的日志记录所在的位置决定了能截到哪如果活动部分正好在日志文件末尾不截断直接缩结果就是几乎没动。-- 第一步截断日志。没有日志备份需求时用 TRUNCATE_ONLY -- 注意这会破坏日志备份链之后必须做一次完整备份才能恢复日志备份 BACKUP LOG 你的库名 WITH TRUNCATE_ONLY GO -- 老版本兼容写法效果相同 -- DUMP TRANSACTION 你的库名 WITH NO_LOG -- GO -- 第二步把日志文件收缩到指定大小单位 MB不要填 0 或 1 -- 第一个参数用 2.2 节查到的日志文件 fileid DBCC SHRINKFILE (2, 256) WITH NO_INFOMSGS GO日志的目标大小不要写 0。0 在 SHRINKFILE 里的含义是收缩到文件创建时的初始大小如果当初建库时日志初始大小就是 1MB会缩成一个极小的日志文件下一次大事务立刻又涨回去还可能因为增长次数太多产生大量日志碎片。合理的做法是按业务峰值事务量给一个固定值256MB 到 1GB 之间具体看每天最大的批量操作有多大。数据文件同理用查到的主数据文件 fileid-- 收缩主数据文件到初始大小0 表示创建时的初始大小不是 0MB DBCC SHRINKFILE (1, 0) WITH NO_INFOMSGS GO -- 如果 0 缩过头可以给一个明确的 MB 值比初始值大一些 -- DBCC SHRINKFILE (1, 2048) -- GO这里有个常见误解很多人以为第二个参数写 0 就是缩到最小。实际含义是缩到文件创建时的初始大小这个值可能远大于实际数据量也可能小于你想要的结果。想看真实能缩到多少先跑一次 SHRINKDATABASE 看 EstimatedPages再决定填 0 还是填具体 MB。3.3 DBCC UPDATEUSAGE 修的是统计口径不是空间前三步跑完文件大小已经变了但系统表里记录的页数、行数可能还是旧的。大量 DELETE 之后sysindexes 里的页计数会虚高导致 sp_spaceused 显示的空间和实际不符下次再收缩时估算的目标也是错的。UPDATEUSAGE 干的就是重算这些计数。-- 0 表示当前数据库也可以写库名 DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS GO -- 只想修某张表时可以精确到表名 -- DBCC UPDATEUSAGE (0, 流水表) -- GO它不释放任何磁盘空间跑完文件大小不会变但下一次收缩的估算会准sp_spaceused 的数字也终于能对上。大库上这个命令可能跑很久因为要扫所有表和索引的页分配信息放窗口期执行如果只是个别表删得狠精确到表名能省不少时间。3.4 串成一份可回放的脚本把四步写成一份带变量的脚本下次换库只改变量。注意每步之间不要跳尤其日志截断必须在日志收缩之前SET NOCOUNT ON DECLARE dbname sysname DECLARE logfile int DECLARE datafile int DECLARE logmb int SET dbname 你的库名 SET logmb 256 -- 日志目标大小按业务峰值定 -- 1. 取文件 ID SELECT logfile fileid FROM sysfiles WHERE groupid 0 SELECT datafile fileid FROM sysfiles WHERE groupid 1 AND fileid (SELECT MIN(fileid) FROM sysfiles WHERE groupid 1) PRINT 日志文件 ID CAST(logfile AS VARCHAR(10)) PRINT 数据文件 ID CAST(datafile AS VARCHAR(10)) -- 2. 整库收缩保留 10% 可用空间 DBCC SHRINKDATABASE (dbname, 10) WITH NO_INFOMSGS -- 3. 截断日志后单独缩日志文件 BACKUP LOG 你的库名 WITH TRUNCATE_ONLY DBCC SHRINKFILE (logfile, logmb) WITH NO_INFOMSGS -- 4. 缩数据文件到初始大小 DBCC SHRINKFILE (datafile, 0) WITH NO_INFOMSGS -- 5. 修正统计口径 DBCC UPDATEUSAGE (0) WITH NO_INFOMSGS -- 6. 看结果 EXEC sp_spaceused SELECT fileid, groupid, name, size * 8 / 1024 AS size_mb FROM sysfiles GO脚本里的 logmb 是唯一需要按环境调的参数日报类业务 256MB 足够有 nightly 批量导入的给 1GB。datafile 取主文件组里最小的 fileid多文件库请改成按 size 最大来选。4. 避坑五次翻车记录与现场处置这套命令我在不同环境上跑过很多次真正出问题的从来不是命令写错而是对状态的判断错。下面五条按现象 → 原因 → 解决记都是当场处置过的。4.1 收缩完几乎没变小日志没截断现象DBCC SHRINKFILE 跑完日志文件从 4GB 变成 3.9GB几乎没动重复跑也没用。原因日志的活动部分在文件末尾未截断的情况下 SQL Server 不能越过活动部分收缩。FULL 恢复模式下如果长期没做日志备份日志会一直保留缩多少都是白费。解决先执行BACKUP LOG 库名 WITH TRUNCATE_ONLY或临时切 SIMPLE 模式并做一次 CHECKPOINT再缩。缩完记得补一次完整备份因为 TRUNCATE_ONLY 已经破坏了日志链之前的日志备份无法再用于时间点恢复。这条是血泪经验别省。4.2 fileid 猜错把数据文件缩到初始大小现象本来只想缩日志执行DBCC SHRINKFILE (1, 0)之后日志没变数据文件从 6GB 掉到 800MB业务报表直接报空间不足。原因这台机器的库里 fileid 1 是主数据文件日志是 2和1 数据 2 日志的口诀正好对上但另一次环境里因为文件被重建过顺序反了。靠猜必翻车。解决回到 2.2 节的查询用 groupid 判断类型脚本里把 fileid 查出来赋给变量不要手写常量。已经缩错了的只能靠备份恢复或者让文件重新增长——但增长回来的空间分布是新的索引碎片会很严重要重建索引。4.3 收缩后查询变慢页碎片和索引碎片现象磁盘腾出来了第二天开始有人反馈某张表的查询从 1 秒变成 8 秒执行计划没变。原因收缩是把页从文件尾部往前搬搬的过程中页的顺序被打乱物理读从顺序扫描退化成随机读同时索引页的填充度被打散扫描密度下降。解决收缩完必须做一次索引维护。先DBCC SHOWCONTIG看扫描密度和页密度再决定用DBCC INDEXDEFRAG在线、慢、不重建还是DBCC DBREINDEX离线、快、彻底。经验值扫描密度低于 90% 就重建大表放维护窗口。4.4 自动收缩开着业务高峰被拖住现象每天上午十点左右系统整体变慢CPU 不高磁盘队列很长没有明显慢查询。原因数据库开了 autoshrink后台收缩线程在高峰时检测到文件有空闲就启动移动页的同时和业务读写抢 IO。收缩越频繁碎片越多下次更慢形成循环。解决EXEC sp_dboption 库名,autoshrink,FALSE关掉改成定期维护窗口手工收缩并把文件的 growth 设成固定 MB 而不是百分比减少增长次数。关掉之后 IO 抖动通常当天就消失。4.5 没备份就下手回滚无门现象收缩中途查询分析器连接断开再连上发现数据库处于恢复状态业务停了两个小时。原因收缩大库耗时长期间有人重启了实例或者磁盘写满事务回滚加上日志恢复把时间拉长更糟的是没做备份出问题只能等。解决操作前必做完整备份并 RESTORE VERIFYONLY 验证大库收缩期间把库设为单用户或限制连接确认备份盘和数据库盘不是同一块物理盘。这套流程后来我做成检查清单缺一项就不动手。5. 收缩之后的善后碎片、增长参数与验收收缩只解决文件多大不解决用起来快不快。真正决定这次操作值不值的是收完之后的三件事碎片有没有清、增长参数有没有调、验收数字有没有留档。5.1 用 SHOWCONTIG 看碎片用 DBREINDEX 重建先量化碎片别凭感觉重建。SHOWCONTIG 关注两个指标扫描密度Scan Density越接近 100% 越好逻辑扫描碎片Logical Scan Fragmentation越低越好。-- 看单张表的碎片情况 DBCC SHOWCONTIG (流水表) WITH ALL_INDEXES, TABLERESULTS GO -- 重建整表所有索引填充因子 90留出插入空间 DBCC DBREINDEX (流水表, , 90) GO -- 大表不想锁太久用索引整理代替重建在线碎片清理不彻底 -- DBCC INDEXDEFRAG (0, 流水表, 1) -- GODBREINDEX 会重建索引页顺带把数据页也重新组织效果最彻底但需要额外空间大约等于该表索引大小并且在 SQL Server 2000 上是阻塞的必须放维护窗口。INDEXDEFRAG 是在线操作业务可以继续跑但只做页内整理碎片清不干净适合白天做小修补。重建完成后再跑一次 SHOWCONTIG 对比扫描密度从 70% 回到 99% 是常见结果。这一步不做第 4.3 条的变慢几乎是必然的。5.2 增长参数怎么改才不反复收缩完最怕的是文件很快又涨回去涨回去的过程就是碎片产生的过程。把 growth 从百分比改成固定 MB是成本最低的改善对象建议配置理由数据文件 growth固定 256MB 或 512MB百分比增长在大库上单次增长过大造成写入尖峰数据文件 maxsize按磁盘规划设上限不要用 -1避免某个库把磁盘吃光拖垮实例日志文件 growth固定 128MB 或 256MB小步增长会产生大量日志碎片日志文件 maxsize留出 2 倍峰值事务量太小会频繁增长太大可能撑爆磁盘autoshrink关闭后台收缩与业务抢 IO-- 修改数据文件的增长方式SQL Server 2000 支持 MODIFY FILE ALTER DATABASE 你的库名 MODIFY FILE ( NAME 你的逻辑文件名, FILEGROWTH 256MB, MAXSIZE 20480MB ) GO -- 日志文件同理逻辑名从 sysfiles 的 name 列取 ALTER DATABASE 你的库名 MODIFY FILE ( NAME 你的日志逻辑名, FILEGROWTH 128MB, MAXSIZE 8192MB ) GO改完用EXEC sp_helpfile复核确认 growth 列显示的是 MB 而不是百分比。5.3 验收三张数字对比收缩前后操作做完别急着收工留一份前后对比既验证效果也给下次操作留基线。至少要对比三组数sysfiles 里的文件大小、sp_spaceused 的逻辑占用、SHOWCONTIG 的碎片率。指标收缩前收缩后判断标准数据文件 sizeMB记录实测值记录实测值下降幅度应接近 unused 的量日志文件 sizeMB记录实测值记录实测值应接近设定的目标值sp_spaceused 的 unused记录实测值记录实测值收缩后应显著下降扫描密度记录实测值重建后记录应回到 95% 以上逻辑扫描碎片记录实测值重建后记录应低于 5%对比表里如果文件变小了但 unused 没降说明收缩只是搬了页没释放空间回去检查是不是用了 NOTRUNCATE如果unused 降了但扫描密度还是很低回去补 DBREINDEX。两个都对上这次操作才算收干净。6. 批量体检脚本一次看清所有库能压出多少单库处理熟了之后真正费时间的是到底哪个库值得缩。一台实例上几十个库逐个点开看 sysfiles 不现实。写个游标扫一遍 sysdatabases把所有库的文件大小、类型、增长方式打出来按大小排序先缩最大的那个。-- 在 master 库执行扫描实例上所有用户数据库的文件情况 SET NOCOUNT ON DECLARE db sysname DECLARE sql nvarchar(2000) CREATE TABLE #files ( dbname sysname, fileid int, groupid int, logical sysname, size_mb int, growth_mb int ) DECLARE cur CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE dbid 4 -- 排除 master/model/msdb/tempdb AND status 512 0 -- 排除正在恢复的库 OPEN cur FETCH NEXT FROM cur INTO db WHILE FETCH_STATUS 0 BEGIN SET sql INSERT INTO #files SELECT db , fileid, groupid, name, size * 8 / 1024, CASE WHEN status 0x100000 0x100000 THEN -1 ELSE growth * 8 / 1024 END FROM [ db ].dbo.sysfiles EXEC (sql) FETCH NEXT FROM cur INTO db END CLOSE cur DEALLOCATE cur -- 按库汇总一眼看出谁最占地方 SELECT dbname, SUM(CASE WHEN groupid 0 THEN size_mb ELSE 0 END) AS log_mb, SUM(CASE WHEN groupid 0 THEN size_mb ELSE 0 END) AS data_mb, SUM(size_mb) AS total_mb, MAX(CASE WHEN growth_mb -1 THEN 1 ELSE 0 END) AS has_pct_growth FROM #files GROUP BY dbname ORDER BY total_mb DESC DROP TABLE #files GO脚本里status 512用来过滤正在恢复的库growth_mb -1标记出还在用百分比增长的库这些库优先改配置。跑出来的 total_mb 排序就是处理顺序一次缩一个缩完做索引重建别图快连着缩好几个——IO 顶不住的时候整台实例都会跟着卡。还有一种情况这个脚本会暴露出来某个库 data_mb 不大但 log_mb 特别大。这种库八成是 FULL 恢复模式长期没做日志备份先查恢复模式和备份作业别急着缩缩了还会涨回去。有一个边界得说清楚如果某个库缩完两三天又涨回原样说明它的空间是被真实业务写回来的不是空洞这时候再缩就是纯粹的折腾。合适的做法是评估归档周期把历史数据迁走或者用新建库 BCP/DTS 导入的方式做一次物理重整比反复收缩干净得多。从那以后我每次动手前都会先把 2.2 节那条 sysfiles 查询跑一遍、把备份验证跑一遍缺一步就不敲 SHRINKFILE。数据库这东西快是次要的能回退才是第一位的。希望帮到你。本文还有配套的精品资源点击获取