SQL Server 2000数据库的事务日志文件过大，如何将其缩小？

jh4617233

在SQL  Server中，所有对数据库执行的更新操作都会记录在数据库的事务日志文件中，除非将数据库设为可自动收缩的或手动的对数据库进行了收缩，否则事务日志文件将一直增长，直到达到事先设定的日志文件增长上限或用尽所有可用的磁盘空间。

如果当前的数据库文件或日志文件过大，可以使用以下两个命令对其进行收缩：

DBCC  SHRINKDATABASE：收缩指定数据库的所有数据和日志文件的大小
DBCC  SHRINKFILE：收缩数据库的某个指定数据或日志文件的大小

这两个命令可以释放数据库中的空闲空间，并将数据库或指定的数据库文件收缩到指定的大小，但收缩后的数据文件或日志文件的大小不会小于文件中现存的有效数据所占空间的大小。

关于这两个命令的具体使用方法，可以参考SQL  Server  2000联机丛书中的相应主题。另外，也可在SQL  Server企业管理器中执行数据库收缩，同样是调用的以上两个命令，效果类似。

在使用以上命令收缩日志文件的时候需要注意，已写入数据库但未被截断的事务日志记录是不会被收缩的，因为虽然这部分日志记录的信息已经写入数据库文件，但在使用事务日志备份进行数据库还原的时候，还将用到其中的信息。

对于使用简单恢复模型的数据库，事务日志会在每次处理检查点（CheckPoint）时自动被截断。
对于使用完全恢复模型或大容量日志记录恢复模型的数据库，事务日志只有在执行日志备份（BACKUP  LOG）时才会被截断，这时事务日志中记录的信息被写入事务日志备份文件，而它们所占用的这部分空间被标记为可用（即被截断）。

截断事务日志并不会使日志文件变小，但可以将其中的部分空间释放供以后写入新的日志记录使用。若要减少日志文件的物理大小，则要使用上面提到的DBCC  SHRINKDATABASE和DBCC  SHRINKFILE命令。

在执行BACKUP  LOG语句的时候，还可以使用WITH  NO_LOG（或WITH  TRUNCATE_ONLY，含义相同）参数，这时并不真正备份事务日志，而只是截断事务日志中的非活动部分（这和普通的BACKUP  LOG语句作用相同）。这适合于剩余磁盘空间不够进行事务日志备份或不打算保留事务日志中的非活动部分用于数据库恢复的情况。

为避免事务日志文件增长过快以致用尽所有磁盘空间的现象发生，一种办法是将数据库设为使用简单恢复模型，这样可以使SQL  Server周期性的自动截断事务日志的非活动部分，并回收其占用的空间供以后写入事务日志记录使用。但这将使数据库无法利用事务日志备份还原到即时点，降低了数据库的可靠性，因此一般不应用于生产型数据库。

对于生产型数据库，推荐的做法是使用完全恢复模型，并定期进行数据库的完全备份和事务日志备份。例如每周执行一次完全备份，每天执行一次事务日志备份，这可以通过SQL  Server企业管理器中的数据库维护计划向导很方便的实现（一般可以设为在每天夜里业务不繁忙的某个时刻自动执行备份）。

通过定期执行数据库的事务日志备份，可以避免日志文件的迅速增大，而使其保持一个比较稳定的大小。

虽然数据库备份文件也会占用很多磁盘空间，但随时可以将这些文件移到其他磁盘上或在不需要它们的时候将其删除，而且可以在出现故障或误操作的时候方便的进行数据库的还原。

由于数据文件的大小是随数据库中数据量的增长而增长的，数据库中已删除的数据所占的空间可以供新插入的数据使用；而在定期执行了事务日志的备份后，我们可以将日志文件的大小控制在一个比较合理的范围。因此，一般不需要对数据库进行收缩，也不推荐将数据库设为自动收缩模式。建议仅在以下情况下执行数据库的收缩：

1、磁盘空间不足
2、数据文件很大，但其中只包含较少量的数据（可能是以前有大量数据，但后来删除了很多），并且预期今后数据库中的数据量也不会很大。
3、由于长期未进行事务日志备份，导致事务日志文件过大。

减小事务日志文件大小的另一种方法是：首先在该数据库中执行CHECKPOINT命令，然后将该数据库分离（Detach），再将与其对应的数据库日志文件（.ldf文件）改名或删除或移动到其他目录下，然后执行sp_attach_single_file_db存储过程或在企业管理器中重新将其附加（Attach）。由于找不到原来的日志文件，SQL  Server将自动为该数据库建立一个大小只有504K的日志文件。但这种方法必须暂时将数据库脱机，因此一般不适宜在生产环境中使用。

如果当前数据库的事务日志文件过大，必须对其进行收缩的话，建议参照以下步骤：

1、建议首先备份数据库（但不是必需的）：
BACKUP  DATABASE  database_name  TO  backup_device

2、备份事务日志：
BACKUP  LOG  database_name  TO  backup_device
如果不需要当前事务日志中的记录进行数据库还原或没有足够的空间进行事务日志备份的的话，也可仅执行以下命令截断事务日志：
BACKUP  LOG  database_name  WITH  NO_LOG

3、收缩事务日志文件：
DBCC  SHRINKFILE  (log_file_name)
其中log_file_name是事务日志文件的逻辑名称，可以在企业管理器中数据库属性的“事务日志”页中看到（如Northwind数据库的默认事务日志文件逻辑名称为Northwind_log）。

4、如果日志文件仍然较大的话，可以尝试重复执行一次BACKUP  LOG  WITH  NO_LOG和DBCC  SHRINKFILE命令。

5、如果这时仍没有明显的效果，请执行DBCC  OPENTRAN  (database_name)检查当前数据库中是否存在长时间未提交的活动事务。有必要的话，可以断开这些连接并重新尝试截断事务日志和收缩日志文件。

6、事务日志文件收缩完成后，建议立即执行一次数据库的完全备份并根据实际需要制定适当的数据库备份计划。
摘自 用友政务 经验总结

clc-1202

多好的资料，不学太可惜了。
楼主大好人！！！！！！！！！！！！！！！！！！！！

相思楼栏

谢谢楼主，楼主真伟大！！！！

c267002600

正需要呢   来学习下

zzxc1210

正需要呢   来学习下

gaoyanglion

讲的很全面 不错

kk329831974

这个经典  不仅仅可以解决政务的问题

hhjluck

回复 1# jh4617233


    很好的资料，谢谢楼主

amwrdfiyt

是否有工具呢？有和命令怕出错，恩

crazykiss

很好的资料啊，值得学习

lzlhy4766

嗯。的确不错。拜谢楼主···

yyxms

删除是一个不错的选择

QGB

保存保存。我们服务器总是出这问题

lys2016

楼主辛苦了 。。支持下 。。。。

Together°

学习 能用上