如何防止SQL Server数据库的事务日志异常增长

server|数据|数据库

当事务日志扩展到无法接受的限度时您必须执行的步骤。事务日志的扩展会导致 Microsoft SQL Server 数据库无法使用。

  在 SQL Server 2000 中,每个数据库都至少包含一个数据文件和一个事务日志文件。SQL Server 2000 在该数据文件中以物理方式存储数据。事务日志文件存储您对 SQL Server 数据库执行的所有修改的详细信息,以及执行每个修改的事务的详细信息。由于事务完整性被视为 SQL Server 的一个基本而固有的特点,因此不能在 SQL Server 2000 中关闭对事务详细信息的记录。

  在逻辑意义上,事务日志文件被划分为更小的段,这些段被称为虚拟日志文件。在 SQL Server 2000 中,您可以将事务日志文件配置为根据需要扩展。用户可以控制事务日志的扩展,也可以将其配置为使用所有可用的磁盘空间。SQL Server 对事务日志文件大小所做的任何修改(如截断或增长事务日志文件)都是以虚拟日志文件为单位执行的。

  如果与 SQL Server 数据库相对应的事务日志文件被充满,而且事务日志文件选项被设置为自动增长,则事务日志文件将以虚拟日志文件为单位增长。有时,事务日志文件可能变得非常大,以致没有足够的磁盘空间。如果事务日志文件不断增长,致使日志文件占用了所有可用的磁盘空间而无法继续扩展,则将无法再对数据库执行任何数据修改操作。不仅如此,由于事务日志缺乏扩展空间,SQL Server 可能会将您的数据库标记为可疑数据库。

  减小事务日志的大小

  如果事务日志已经增长到无法接受的限度,而您希望从这种状况中恢复,则必须减小事务日志的大小。为此,必须截断事务日志中非活动的事务并收缩事务日志文件。

  注意:事务日志对于维护数据库的事务完整性非常重要。因此,即使您为数据库和事务日志制作了备份,也不得删除事务日志文件。

  截断事务日志中非活动的事务

  当事务日志增长到无法接受的限度时,您必须立即备份事务日志文件。创建事务日志文件的备份后,SQL Server 会自动截断事务日志的非活动部分。事务日志文件的非活动部分包含已完成的事务,因此,在恢复过程中 SQL Server 将不再用到事务日志文件。SQL Server 会重新利用事务日志中这段被截断的非活动空间,而不会允许事务日志继续增长并占用更多空间。

  还可以通过截断的方法,从事务日志文件中删除非活动的事务。有关截断事务日志的其他信息,请参见 SQL Server 联机丛书中的“截断事务日志”主题。

  重要说明:手动截断事务日志文件后,必须在创建事务日志备份之前创建一个完整的数据库备份。

  收缩事务日志文件

  备份操作或截断方法都不会减小日志文件的大小。要减小事务日志文件的大小,必须收缩事务日志文件。要将事务日志文件收缩到所需大小并删除无用的页面,您必须使用 DBCC SHRINKFILE 操作。DBCC SHRINKFILE Transact-SQL 语句只能收缩日志文件中的非活动部分。

  注意:当 DBCC SHRINKFILE Transact-SQL 语句单独使用时,不能截断日志并收缩日志文件中的已用空间。

  防止事务日志文件异常增长

  要防止事务日志文件异常增长,建议使用以下方法之一:

    • 将事务日志文件的大小设置为一个较大值,以避免事务日志文件自动扩展。

  • 充分评估最佳内存大小后,使用内存单位而不是百分比来配置事务日志文件的自动扩展。

  • 更改恢复模型。如果发生灾难或数据损坏,您必须恢复数据库,以维护数据库数据的一致性和事务的完整性。根据数据在数据库中的重要程度,您可以选择以下恢复模型之一,以便确定如何备份数据以及数据丢失可能给您带来的风险:

    • 简单恢复模型 (SIMPLE)

  • 完全恢复模型 (FULL)

  • 大容量日志记录恢复模型 (BULK-LOGGED)

  使用简单恢复模型,您可以将数据库恢复到最近的数据库备份。使用完全恢复模型或大容量日志记录恢复模型,您可以通过使用事务日志文件备份来还原数据库,这样可以将数据库恢复到故障发生时的故障点。

  默认情况下,在 SQL Server 2000 中,SQL Server 数据库的恢复模型被设置为完全恢复模型。在完全恢复模型中,会定期备份事务日志,从而防止事务日志文件增长得过大,以致与数据库大小相比严重失衡。相比之下,如果不执行事务日志的定期备份,事务日志文件会不断增长,直至充满整个磁盘,而且您可能无法对 SQL Server 数据库执行任何数据修改操作。

  如果您不希望在灾难恢复操作过程中使用事务日志文件,则可以从完全恢复模型更改为简单恢复模型。

  • 定期备份事务日志文件,删除事务日志中非活动的事务。

  • 将事务设计为小型事务。

  • 确保没有任何未遂事务继续无限期地运行。

  • 将“更新统计”选项安排为每天运行。

  • 要对索引进行碎片整理以改善生产环境中的工作负荷性能,请使用 DBCC INDEXDEFRAG Transact-SQL 语句而不是 DBCC DBREINDEX Transact-SQL 语句。如果运行 DBCC DBREINDEX 语句,当 SQL Server 数据库处于完全恢复模式时,事务日志可能会大大扩展。此外,DBCC INDEXDEGRAG 语句不像 DBCC DBREINDEX 语句那样长时间持有锁。

  如果您必须运行 DBCC DBREINDEX 语句,因为这是一个作业,是数据库维护计划的一部分,则必须将该作业分解为多个作业。此外,在执行这些作业的间歇,还必须经常备份事务日志。

时间: 2016-05-07

如何防止SQL Server数据库的事务日志异常增长的相关文章

使用SQL Server 2008的事务日志传送功能备份数据库

一:系统要求 数据库服务器,名称Server-DW,Windows Server 2003 X64,安装SQL Server 2008,需要备份的数据库为"JKL_DW". 备份服务器,名称Server-ETL,Windows Server 2003 X32,安装SQL Server 2008. 二:准备工作 1.在Server-ETL上创建文件夹,用于存放备份数据库,此例中命名为"jklDW". 2.在Server-ETL上创建文件夹,用于存放为备份数据传送过来的

SQL Server数据库中交易日志的维护

交易日志(Transaction logs)是数据库结构中非常重要但又经常被忽略的部分.由于它并不像数据库中的schema那样活跃,因此很少有人关注交易日志. 交易日志是针对数据库改变所做的记录,它可以记录针对数据库的任何操作,并将记录结果保存在独立的文件中.对于任何每一个交易过程,交易日志都有非常全面的记录,根据这些记录可以将数据文件恢复成交易前的状态.从交易动作开始,交易日志就处于记录状态,交易过程中对数据库的任何操作都在记录范围,直到用户点击提交或后退后才结束记录.每个数据库都拥有至少一个

MS SQL Server数据库清理错误日志的方法

SQL错误日志记录了数据库运行过程的遇到的各种问题及一些重要信息,作为排错需要,我们通常都不会主动去清理这些日志文件,只有每次重启服务器时,SQL会自动删除时间最老的日志文件,并新生成一个日志文件. 通过在服务器上查看数据库的日志文件,发现存在大量的query notification dialog的信息,而且出现的频率非常的高,导致日志文件增大非常快. 通过google了解到这个错误跟service broker的消息机制由关系,可以通过使用跟踪标记:DBCC TraceOn(4133,-1)

SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因

原文:SQL Server 中的事务与事务隔离级别以及如何理解脏读, 未提交读,不可重复读和幻读产生的过程和原因 原本打算写有关 SSIS Package 中的事务控制过程的,但是发现很多基本的概念还是需要有 SQL Server 事务和事务的隔离级别做基础铺垫.所以花了点时间,把 SQL Server 数据库中的事务概念,ACID 原则,事务中常见的问题,问题造成的原因和事务隔离级别等这些方面的知识好好的整理了一下. 其实有关 SQL Server 中的事务,说实话因为内容太多, 话题太广,稍

SQL Server 为什么事务日志自动增长会降低你的性能

原文地址:点击打开链接   在这篇文章里,我想详细谈下为什么你要避免事务日志(Transaction Log)上的自动增长操作(Auto Growth operations).很多运行的数据库服务器,对于事务日志,用的都是默认的日志文件大小和自动增长设置.人们有时会很依赖自动增长机制,因为它们刚好能正常工作.当然,如果它正常工作的话,你不必太关注它,但很快你会发现会有问题出现.   只依赖于事务日志的自动增长机制总不是个好主意.首先它会导致严重的日志碎片(Log Fragmentation),在

教你维护SQL Server数据库日志。

[导读]教你维护SQL Server数据库日志. 交易日志(即oracle中的事务)(Transaction logs)是数据库结构中非常重要但又经常被忽略的部分.由于它并不像数据库中的schema那样活跃,因此很少有人关注交易日志. 交易日志是针对数据库改变所做的记录,它可以记录针对数据库的任何操作,并将记录结果保存在独立的文件中.对于任何每一个交易过程,交易日志都有非常全面的记录,根据这些记录可以将数据文件恢复成交易前的状态.从交易动作开始,交易日志就处于记录状态,交易过程中对数据库的任何操

SQL Server数据库日志已满的解决方法

Server Error in '/' Application.</pre><pre>--------------------------------------------------------------------------------</pre><pre> 数据库教程 'databasename' 的日志已满.请备份该数据库的事务日志以释放一些日志空间. 出现如上错误,请把数据库的故障恢复模式改为"简单"模式 , 即可解决 此

从日志中恢复SQL Server数据库到时间点

server|恢复|数据|数据库 Author:David EulerDate: 2004/10/16Email:de_euler-david@yahoo.com.cn 有任何问题,请与我联系:)          DB2中可以使得数据库回复到指定的时间点,SQL Server数据库的Recovery Model为full 或者Bulk copy的时候,是可以从日志来恢复数据库的.实际上日志中记录的一条一条的transact sql语句,恢复数据库的时候会redo这些sql语句.  前提条件:m

[Java]log4j写SQL Server数据库日志的统一写法

[Java]log4j写SQL Server数据库日志的统一写法 编写者 日期 关键词 郑昀@ultrapower 2005-7-19 Java log4j sqlserver   SQL Server 2000 Driver for JDBC Service Pack 3的下载 之前需要注意一点,sql server同样必须打上了sp3的补丁,然后才能与jdbc for sqlserver sp3连接.如果是"Error establishing socket"错误,很有可能是要在s