简介针对SQL Server 2000数据库在频繁执行删除操作后数据库文件出现大量冗余空间而企业管理器压缩效果不佳的问题这份Word文档提供了一套基于DBCC命令的深度压缩方法。文档适合正在维护老版本数据库的运维人员与数据库管理员内容从如何查看数据库文件标识开始逐步说明了将整个数据库收缩、再单独收缩日志文件与数据文件、最后更新文件使用情况这一完整流程并给出了每条命令对应的作用与注意事项。文中特别强调压缩前必须进行完整备份同时客观分析了深度压缩可能造成的文件碎片增加和输入输出性能下降等影响有助于读者在实际生产环境中谨慎操作。资源包共1个docx文件大小仅256KB说明精炼、步骤清晰便于快速参照实施。已有310人学习下载适合希望在不升级系统的前提下解决数据库文件膨胀问题的读者参考。1. 老系统磁盘告警Sqlserver2000的数据库文件为什么越撑越大在维护Sqlserver2000实例时我最常见的一个场景就是数据库文件比实际数据大出几倍日志文件动辄几十GB磁盘红盘后整个库都停摆。这里说的压缩数据库文件指的是用DBCC SHRINKFILE把物理文件收缩到接近实际使用量尤其是把日志文件压到最小而不是压缩备份或压缩数据行。这个问题在Sqlserver2000上尤其突出因为它的自动增长策略和日志机制都比较原始一旦没人盯着文件大小就刹不住。本文要讲的就是怎么安全地做深度压缩、收缩到什么程度、以及压缩后为什么必须重建索引里面有不少血泪经验希望帮你少踩几个坑。2. 压缩之前先看懂Sqlserver2000的文件增长逻辑2.1 数据文件与日志文件两种截然不同的膨胀方式SQL Server 2000的每个数据库至少有两个文件数据文件.mdf和日志文件.ldf。数据文件存放表和索引以8KB页为单位分配日志文件存放事务记录以512字节扇区为最小单位但在逻辑上以“日志记录”为单位连续增长。数据文件膨胀通常是因为插入了大量数据后没有清理删除了数据但文件大小不会自动收缩日志文件膨胀则是因为事务日志没有及时截断或者有长事务持续写入。判断当前文件大小和空闲量我一般会先执行下面的查询USE YourDatabase GO -- sysfiles视图能看到逻辑名、物理文件名和当前大小单位是8KB页 SELECT name, filename, size * 8 / 1024 AS 大小MB, maxsize, status FROM sysfiles GO这里的size字段以8KB页为单位乘以8再除以1024就得到MB。maxsize显示文件能否自动增长status里能看到文件是数据还是日志。执行后你就能看到整个数据库的物理构成数据文件占多少、日志文件占多少。正常情况下日志文件应该是几MB到几十MB如果看到几十GB那一定是截断策略出了问题。还有一个常用命令是sp_helpdb它会列出每个文件的逻辑名和当前大小EXEC sp_helpdb YourDatabase GO这个命令适合快速查看但要精确到文件组或页数还是sysfiles更好。理解了这两类文件的膨胀机制你才会明白深度压缩不能一概而论数据文件有真实数据收缩到最小只意味着把尾部空闲页释放掉日志文件几乎全部是重做记录截断后收缩可以掉到很小的值。2.2 压缩前必须完成的检查清单深度压缩有一个前置条件数据库状态必须正常不能处于单用户模式或正在还原。更重要的是必须在压缩前做一次完整备份。这个备份既是安全兜底也是日志收缩的前提——SQL Server 2000的日志收缩需要先截断日志截断意味着丢弃已备份的日志记录。如果你没做完整备份就截断日志数据库会失去日志链在恢复时可能无法还原到最近时间点。我的检查清单一般是这样的完整备份数据库确认备份文件有足够的保留期。查看当前日志文件大小和日志空间使用率。查看数据库的恢复模式简单恢复模式可以直接截断日志完整恢复模式需要先备份日志。确认没有长时间运行的慢查询或未提交事务。记录当前文件大小收缩后用作对比。检查恢复模式的命令SELECT DATABASEPROPERTYEX(YourDatabase, Recovery) AS 恢复模式 GO如果是完整恢复模式建议先执行BACKUP LOG后面会详细说。如果业务允许也可以临时改成简单模式但这是有副作用的我会在避坑章节展开。2.3 选择收缩命令SHRINKDATABASE还是SHRINKFILESQL Server 2000提供了两种收缩命令DBCC SHRINKDATABASE和DBCC SHRINKFILE。很多新手喜欢用前者因为它一次收缩所有文件DBCC SHRINKDATABASE(YourDatabase, 10) GO但我不推荐在生产环境用SHRINKDATABASE原因有三个第一它只能指定一个百分比上面10表示收缩到剩余10%空闲不能精确控制每个文件的目标大小第二它对数据和日志一视同仁但日志文件可能不需要收缩那么多第三它会在所有文件上执行页重定位消耗大量IO容易阻塞。深度压缩应该用DBCC SHRINKFILE因为它可以指定逻辑文件名和目标大小单位是MB精确度更高也不会动其他文件。举个例子如果你知道日志文件应该收缩到5MB直接写DBCC SHRINKFILE (YourDatabase_Log, 5)就够了。后面我会给出完整的命令链。3. 执行深度压缩DBCC SHRINKFILE的命令链与参数详解3.1 精准收缩数据文件语法与目标大小怎么定数据文件的收缩要谨慎因为你不能把文件缩到比实际数据占用还小。我一般会在收缩前先查看表空间的占用再算出一个目标值。数据文件收缩的命令是USE YourDatabase GO -- 先查询文件逻辑名 SELECT name, filename, size * 8 / 1024 AS 当前MB FROM sysfiles WHERE groupid 1 OR groupid 0 GO -- 然后收缩到目标大小单位MB DBCC SHRINKFILE (YourDatabase_Data, 2048) GO这里YourDatabase_Data是数据文件的逻辑名2048是目标大小MB。DBCC SHRINKFILE会从文件的末尾开始释放页把文件压到2048MB。如果当前数据实际占用已经超过2048MB这个命令会失败或收缩不到目标值它会先尝试把可用空间释放出来但如果中间有被占用页跨越目标边界就不能完全达到目标。执行后你会发现文件变小了但可能没到2GB。这是因为数据文件的存储结构是分页的页的位置散落收缩操作需要把页往文件头部移动移动过程中会产生碎片。这也是为什么收缩后必须重建索引我后面专门说。还有一个常用参数是TRUNCATEONLY它只释放文件末尾的空白空间不做页重定位。在某些情况下速度很快但只能减少文件末尾的空闲实际能回收的空间有限。命令如下DBCC SHRINKFILE (YourDatabase_Data, TRUNCATEONLY) GO注意TRUNCATEONLY在这种情况下不需要指定目标大小它会释放所有文件末尾的空闲页然后把文件物理大小减到最后一个已分配页的位置。这个操作的副作用最小但收缩效果可能不如指定目标大小。3.2 日志文件收缩截断、备份与收缩的完整顺序日志文件的收缩是深度压缩的重头戏。SQL Server 2000的日志文件通常带_Log后缀的逻辑名。收缩的完整顺序是截断日志 - 收缩文件 - 验证。如果你处于完整恢复模式必须先备份日志才能截断USE YourDatabase GO -- 完整备份日志 BACKUP LOG YourDatabase TO DISK D:\backup\YourDatabase_log_backup.bak GO -- 收缩日志文件到5MB DBCC SHRINKFILE (YourDatabase_Log, 5) GO如果你不想保留日志备份比如测试环境可以用BACKUP LOG WITH TRUNCATE_ONLY来截断日志但不产生备份文件这在SQL Server 2000上是合法的虽然微软后续版本已经废弃了这个选项BACKUP LOG YourDatabase WITH TRUNCATE_ONLY GO DBCC SHRINKFILE (YourDatabase_Log, 5) GO这里有个关键参数目标大小5表示收缩到5MB。为什么是5而不是0因为日志文件至少需要能记录当前活动事务太小会导致后续事务失败。0代表收缩到最小可能大小但我不建议在日志上直接写0因为最小大小可能变成一个极小的值比如0.5MB后续一个稍微大点的操作就会立刻触发自动增长带来IO抖动。5MB到10MB是一个比较安全的起始值。还有一个点SHRINKFILE在执行时会对日志文件加锁如果在业务高峰执行可能会阻塞需要写日志的事务。所以日志收缩应该安排在维护窗口里比如凌晨。3.3 用脚本批量收缩多个数据库如果你维护的是十几台服务器的集群手动一台台执行太慢。SQL Server 2000支持用游标遍历所有数据库动态生成收缩命令。下面是我常用的自动化脚本SET NOCOUNT ON DECLARE dbName sysname DECLARE logFileName sysname DECLARE dataFileName sysname DECLARE sql NVARCHAR(4000) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (master, model, msdb, tempdb) OPEN db_cursor FETCH NEXT FROM db_cursor INTO dbName WHILE FETCH_STATUS 0 BEGIN -- 获取每个库的日志逻辑名 SELECT TOP 1 logFileName name FROM [dbName].dbo.sysfiles WHERE status 0x40 ! 0 -- 动态拼接收缩日志命令 SET sql NUSE [ dbName N] DBCC SHRINKFILE ( logFileName N, 10) EXEC sp_executesql sql FETCH NEXT FROM db_cursor INTO dbName END CLOSE db_cursor DEALLOCATE db_cursor GO这段脚本有很多坑首先是动态SQL里的[dbName]不会自动替换真实写法要用quotename其次status 0x40判断file属性不够可靠在2000中日志文件的groupid是0更简单的判断是groupid 0。我后来改成更稳的版本SET NOCOUNT ON DECLARE dbName sysname DECLARE logFileName sysname DECLARE sql NVARCHAR(4000) DECLARE db_cursor CURSOR FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN (master, model, msdb, tempdb) OPEN db_cursor FETCH NEXT FROM db_cursor INTO dbName WHILE FETCH_STATUS 0 BEGIN SET sql NUSE QUOTENAME(dbName) N SELECT logFileName name FROM sysfiles WHERE groupid 0 EXEC sp_executesql sql, NlogFileName sysname OUTPUT, logFileName OUTPUT SET sql NUSE QUOTENAME(dbName) N DBCC SHRINKFILE ( QUOTENAME(logFileName) N, 10) EXEC sp_executesql sql FETCH NEXT FROM db_cursor INTO dbName END CLOSE db_cursor DEALLOCATE db_cursor GO这个脚本逻辑上更清晰外层游标遍历库内层用sp_executesql带输出参数取日志逻辑名再拼接收缩命令。参数说明里logFileName是日志文件的逻辑名10是目标MB。这里的QUOTENAME很重要否则库名或文件名里带空格就会报错。运行前先在测试库上试一遍避免误伤系统库。4. 深度压缩后的连锁反应碎片化与性能回退4.1 为什么收缩后查询反而变慢页重定位带来的索引碎片压缩数据库文件的核心动作是把页从文件尾部移到头部这个过程会打乱索引的物理顺序。SQL Server 2000的聚集索引依靠键值顺序排列页一旦物理页被挪动逻辑顺序和物理顺序脱节扫描时就会产生大量碎片。碎片会导致额外IO和CPU消耗最典型的表现是收缩前一个查询2秒完成收缩后变成20秒。这不是玄学而是页的顺序被破坏后预读失效磁盘寻道次数增加。我曾在一次深度压缩后线上订单查询接口从平均120ms掉到800ms最终定位到是聚集索引碎片率达68%。所以深度压缩从来不是一个孤立操作它必须和索引维护绑定。在SQL Server 2000里一个注意点是统计数据也会随着页移动而过期需要更新统计信息。4.2 重建索引与更新统计压缩后的后悔药收缩数据文件之后我会立刻重建所有索引。SQL Server 2000没有ALTER INDEX REBUILD所以要使用DBCC DBREINDEX。它的作用是完全重建索引消除碎片并更新统计信息USE YourDatabase GO -- 重建所有用户表的所有索引 EXEC sp_MSforeachtable command1 DBCC DBREINDEX (?) GO这里的sp_MSforeachtable是系统未公开的遍历表存储过程DBCC DBREINDEX不指定索引名时表示重建所有索引。这个命令在收缩后执行代价是重建过程会消耗CPU和IO并加锁阻塞访问。因此更适合放在维护窗口。如果表特别大重建整个索引可能要数小时那我会改用DBCC INDEXDEFRAG它做的是在线碎片整理不阻塞读写但整理效果比重建略差DBCC INDEXDEFRAG (YourDatabase, YourTable, YourIndex) GOINDEXDEFRAG需要指定表名和索引名它按物理顺序重排页同时释放碎片。对于碎片率不高的场景够用但碎片率超过50%我建议还是DBCC DBREINDEX一步到位。重建索引后还应该更新所有表的统计信息EXEC sp_MSforeachtable command1 UPDATE STATISTICS ? GOUPDATE STATISTICS会重新计算数据分布避免查询优化器使用过期的统计信息。4.3 验证压缩结果用sysfiles和sp_spaceused对比压缩完成后验证是必须的。我会用三条命令确认效果USE YourDatabase GO -- 查看文件大小 SELECT name, size * 8 / 1024 AS 现在MB FROM sysfiles GO -- 查看数据库整体空间使用 EXEC sp_spaceused GO -- 查看每个表的实际数据量 EXEC sp_MSforeachtable command1 EXEC sp_spaceused ? GOsp_spaceused会返回database_size和unallocated space两项。如果收缩成功database_size应该接近所有表实际占用加一些元数据开销如果看到unallocated space仍然很大说明收缩没达到预期可能是文件中有无法移动的页面比如正在使用的日志页或有大量页分裂。对比压缩前的记录你可以量化到底释放了多少空间这也是给管理层汇报的关键数据。5. 避坑指南Sqlserver2000压缩文件的七个常见问题排查5.1 收缩日志文件一直失败提示“日志文件无法收缩”现象执行DBCC SHRINKFILE后日志文件大小没有变化错误日志没有明确报错但文件还是几十GB。原因日志文件里有活动日志区也就是从事务开始到现在尚未截断的日志记录收缩操作只能释放文件末尾的非活动页。如果活动日志占用了文件末尾收缩就无从下手。另一个常见原因是数据库启用了复制复制标记阻止日志截断。解决先在简单恢复模式下执行检查点强制截断日志再收缩。命令是CHECKPOINT。如果库使用了完整恢复模式先执行BACKUP LOG然后立刻收缩。如果还是不行检查是否有长事务未提交用DBCC OPENTRAN查看最老的活动事务杀掉或等它结束。DBCC OPENTRAN (YourDatabase) GO CHECKPOINT GO DBCC SHRINKFILE (YourDatabase_Log, 5) GO5.2 收缩数据文件后自动增长立刻触发文件再次膨胀现象把数据文件从20GB缩到5GB隔一天又涨回15GB看起来白做了。原因你把文件缩到5GB但表实际数据已经有10GB收缩失败了吗不SHRINKFILE会把文件缩到目标大小但如果目标小于已使用空间它会先尝试移动页移不动就会扩展回去。实际上如果你硬把文件缩到比数据还小SQL Server会重新自动增长频繁增长会产生大量尾部碎片和写放大。解决收缩前先用sp_spaceused查一下表数据总占用目标大小要大于当前数据总大小留出20%安全余量。同时检查文件自动增长步长建议把最大大小设为一个合理值避免一次性增长几十GB。-- 把增长步长改为100MB而不是默认的10% ALTER DATABASE YourDatabase MODIFY FILE (NAME YourDatabase_Data, FILEGROWTH 100MB, MAXSIZE 20GB) GO5.3 收缩过程长时间阻塞其他会话全部卡死现象执行收缩后应用层传来大量超时报警数据库阻塞率飙升。原因DBCC SHRINKFILE在移动页时会获取表/页上的独占锁SQL Server 2000的锁粒度粗对正在使用的表移动几十万页期间所有对该表的读写都会被阻塞。解决收缩必须放在业务低谷并且最好分批处理。不要用SHRINKDATABASE一下缩全部而是用SHRINKFILE分文件、分时段操作。也可以用NOTRUNCATE参数先做页迁移然后再单独收缩文件末尾但本质上还是需要锁最稳妥的是在维护窗口里做。5.4 日志文件收缩到5MB但运行几个小时后自动膨胀回几十GB现象日志收缩成功但一两个小时后文件又回到原来的大小收缩效果无法持续。原因你的恢复模式是完整模式但日志备份作业没跑起来或者备份间隔太长导致日志不断累积。收缩只能解决当时的大小不能根治增长。如果autoshrink没有开启文件不会自动收缩。解决建立日志备份作业频繁做备份比如每15分钟一次。收缩后把日志文件设一个合理的初始大小。如果业务允许简单恢复模式直接切换这样日志增长会大幅减少。但还是那句话生产环境要在理解后果的前提下切换。5.5 收缩后数据库变成只读或无法访问现象SHRINKFILE执行到一半服务重启然后数据库状态变成Recovery Pending或Suspect。原因日志文件收缩时如果数据库崩溃日志链可能已经断裂重启后无法重放。SQL Server 2000的崩溃恢复机制比较脆弱尤其是在日志文件末尾被截断的情况下。解决收缩前务必完整备份并且设置NORECOVERY不正确做法是先备份再收缩收缩完成后再做一次完整备份。如果真出现了Suspect别着急修复先用备份还原。记住收缩不是日常维护而是节省磁盘的应急手段能减一次是一次不要反复折腾。5.6 在完整恢复模式下用TRUNCATE_ONLY导致日志备份链断裂现象执行了BACKUP LOG WITH TRUNCATE_ONLY之后想还原到某个时刻发现日志备份无法还原数据库恢复到上次完整备份加事务日志时失败。原因TRUNCATE_ONLY会丢弃日志记录等于打破了日志链。SQL Server 2000上的日志备份依赖一个连续日志序列截断后后续日志备份的LSN不连续还原自然失败。解决如果生产库还要求可恢复性不要用TRUNCATE_ONLY改用常规的BACKUP LOG TO DISK。只有测试库或因磁盘满等原因不得不应急时才用TRUNCATE_ONLY。这个坑我踩过一次后来组里约定所有生产库的日志收缩都先做真正的日志备份。5.7 压缩tempdb文件导致系统卡顿现象有人收缩tempdb后整个实例变得极慢甚至CPU高到100%。原因tempdb是共享工作空间收缩tempdb会强制重新分配空间并且所有会话的临时对象都会受影响SQL Server 2000的tempdb如果收缩过小下次排序/哈希操作会频繁触发自动增长产生严重写放大。解决tempdb不允许收缩或者至少在服务器重启后它会自动重建。不要碰tempdb。如果tempdb膨胀严重通常是查询计划有大量排序或临时表优先优化查询而不是收缩。6. 进阶把深度压缩变成一次性的彻底瘦身6.1 使用SHRINKFILE的NOTRUNCATE和TRUNCATEONLY组合拳深度压缩不只是刷一条命令。想让收缩既快又彻底我会分两步走先用NOTRUNCATE把页迁移到文件前部再分离目标大小或TRUNCATEONLY释放末尾空间。例如-- 第一步把页向文件前部迁移不截断文件 DBCC SHRINKFILE (YourDatabase_Data, NOTRUNCATE) GO -- 第二步释放文件末尾空闲空间 DBCC SHRINKFILE (YourDatabase_Data, TRUNCATEONLY) GO这里的NOTRUNCATE和TRUNCATEONLY不能同时使用它们都只做一半事情。NOTRUNCATE迁移页让文件末尾变成纯空闲空间TRUNCATEONLY再把末尾空闲释放不移动页。两步合起来既能理清碎片又能快速回收大部分空间比单步收缩更可控。但注意NOTRUNCATE也会移动页所以仍有碎片风险。收缩完成后必须重建索引。这套组合拳适合大文件比如几百GB的数据文件可以避免一次收缩导致的长时间锁。6.2 用分区/归档根除文件膨胀收缩文件只是救火防火才是正道。在Sqlserver2000上一个有效的长期方案是把历史数据迁移到独立的文件组然后单独收缩那个文件组。具体做法是创建文件组用CREATE TABLE ... ON FileGroup把历史表放到新文件上旧文件只保留活跃数据。这样旧文件收缩时只影响历史表不碰在线业务。另一种做法是定期把过期数据从大表里DELETE然后重建聚集索引。DELETE操作也会在数据文件里留下大量空洞所以配合收缩才有意义。但DELETE本身会写日志可能导致日志膨胀要先备份事务日志再收缩。6.3 一个可持续的维护作业模板我在生产环境长期使用一套三连作业每15分钟备份事务日志每天晚上进行索引碎片整理只对碎片率超过30%的索引每周深夜进行文件收缩。收缩脚本类似这样DECLARE logLogicalName sysname SELECT TOP 1 logLogicalName name FROM YourDatabase.dbo.sysfiles WHERE groupid 0 DBCC SHRINKFILE (logLogicalName, 20) GO设定收缩目标为20MB而不是5MB是为了避免后续事务立刻触发自动增长。维护作业里还要加上失败告警命令执行后检查error如果收缩失败就把错误信息发送到服务器事件日志IF ERROR 0 BEGIN RAISERROR(日志收缩失败, 16, 1) END最后想分享一个教训早期我刚接手老系统时为了贪磁盘空间每周都全库收缩结果每次收缩后都要花半小时重建索引中间还会被同事骂因为锁表。后来我学会了只在磁盘使用率超过85%时才做深度压缩平时就靠日志备份和归档控制大小。深度压缩是手段不是常态合理的文件规划才是长期解。希望这篇文章能把你的Sqlserver2000数据库收拾得服服帖帖祝你好运安全落地。本文还有配套的精品资源点击获取