正文 

当前位置： 首页 > 知识 >

SQL Server 中完整事务日志的实际原因

     2023-04-14     253

关键词：

【中文标题】SQL Server 中完整事务日志的实际原因【英文标题】：Actual cause of full transaction log in SQL Server 【发布时间】：2019-03-25 09:07:30 【问题描述】：

我有一个数据库（恢复模式 = 已满），它的事务日志已满。我知道我可以通过缩小事务日志来解决问题。

但是，我想知道它变满的原因是什么？

因为我读过其他人，他们说大量的插入和删除会增加事务日志的大小。但我无法复制该问题以重现完整事务日志的错误（它以某种方式能够重新分配空间）

问题是： 如何模拟“事务日志已满”的问题？ 如何查看物理事务日志？

该查询用于跟踪当前占用的日志空间，但它不是实际的事务日志，因为查询的占用空间在批量插入和删除过程中是在颤抖而不是静态增加

假设只要不执行备份，日志空间就会增加

DECLARE @TMPTBL AS TABLE (
DatabaseName nvarchar(500),
LogSize decimal(18,2),
LogSpaceUsed decimal (18,2),
[Status] int
) 

INSERT INTO @TMPTBL
EXEC ('DBCC SQLPERF(LOGSPACE)')

SELECT * FROM @TMPTBL WHERE DATABASENAME LIKE '%MYDBNAME%'
GO

【问题讨论】：

您可以使用这篇文章来查找可能会增加日志文件的长时间运行的查询-***.com/questions/941763/… 你现在可以通过完全备份你的数据库来解决这个问题。注意 - 缩小数据库不是解决此问题的正确方法。 我需要查找长时间运行的查询的原因是什么？它们是导致事务日志已满的原因吗？因为我尝试了相当长的删除和插入（4 个表，每个表都包含产生 1 个插入的触发器），需要 4 分钟才能完成，但之后事务日志很好 我知道修复事务日志完整问题的解决方案（手动），我只是好奇为什么它最终会变满:)，如果你能提供脚本让我复制问题（制作事务日志已满） 我只是好奇它是否必须与事务或死锁问题有关 【参考方案1】：

您的问题有多个要点，所以我将尝试逐个回答：

获取日志的大小： 我更喜欢以下内容，因为它包含最大尺寸信息。我并不是说永远不要使用 SQLPERF，我只是在工具箱中添加了另一个工具。还要意识到 SSMS 中有一个磁盘使用情况报告。

SELECT name, size/128 FileSizeInMB
, size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128  AS EmptySpaceInMB
, iif (max_size = -1, null, (max_size)/128) AS MaxSize, iif(max_size > 0, cast(cast((cast(FILEPROPERTY(name, 'SpaceUsed') as decimal) /cast(max_size as decimal)*100) as decimal(18,2)) as varchar), '-') PctFilled
, file_id, type_desc, physical_name
FROM sys.database_files;

检查事务日志中的内容。 您应该探索 fn_dblog。这里有一个简单介绍的链接https://logicalread.com/sql-server-dbcc-log-command-tl01/#.YAnC39hKiUk 这些查询应该可以帮助您入门。这是一个未记录的函数，所以在生产中要小心。

SELECT *
FROM fn_dblog(null, null);

SELECT COUNT(1) as OperationCount,
    SUM (CAST([Log Record Length] as decimal)) as SumRecLen,
    [Operation]
FROM fn_dblog(null, null)
GROUP BY [Operation]    
ORDER BY 2 DESC;

可能的原因： 检查日志的内容将帮助您了解负载的来源。查看具有高 Log Record Length 的操作并继续深入研究。 如果您在列表顶部附近看到操作 LOP_SHRINK_NOOP，您可能需要关闭 Auto Shrink。

如何模拟？ 我猜你的意思是触发 9002 错误。我不确定这会完成什么，但这是一种方法。

    关闭日志的自动增长。 将日志的最大大小设置为比当前大小略小一些。如果已分配大量空白空间，您可能需要备份/压缩日志文件。 执行数据操作，直到达到最大大小。考虑移动大量数据的循环或手动事务。示例：创建一个新的物理表，将 1000 个整数插入其中，然后将所有值重复更新 1。

【讨论】：

日志链和 SQL Server 事务日志 - 健全性检查

】日志链和SQLServer事务日志-健全性检查【英文标题】：LogChainsandSQLServerTransactionLog-sanitycheck【发布时间】：2011-05-0720:52:01【问题描述】：我正在帮助进行临时灾难恢复，我们已经恢复了几周前的数据库备份，然后从事务日志备份... 查看详情

从 SQL Server 的事务日志中删除信息

】从SQLServer的事务日志中删除信息【英文标题】：DeleteinformationfromtransactionlogsatSQLServer【发布时间】：2018-01-0306:50:39【问题描述】：控制台应用程序使用sql请求BACKUPDATABASE创建.bak文件https://technet.microsoft.com/en-us/library/ms191304(v=sql.10... 查看详情

sqlserve日志体系结构(代码片段)

SQL Server 事务日志记录着 undo 及 redo 日志，为了保证数据库在崩溃后恢复，或者在正常数据库操作期间进行回滚，保证数据库事务完整性和持久化。如果没有事务日志记录，数据库在事务上将不一致，并且... 查看详情

如何在 SQL Server 2005 中获取事务日志的逻辑名称

】如何在SQLServer2005中获取事务日志的逻辑名称【英文标题】：HowtogetthelogicalnameofthetransactionloginSQLServer2005【发布时间】：2010-11-2823:41:16【问题描述】：我正在尝试编写一个T-SQL例程，它根据数据库的逻辑名称使用DBCCSHRINKFILE来收... 查看详情

限制 SQL Server 事务日志

】限制SQLServer事务日志【英文标题】：LimitingSQLServerTransactionlog【发布时间】：2020-03-0816:18:10【问题描述】：我目前有一个SQL脚本，它获取具有特定名称的所有表并从中删除记录。这些表中有数百万条记录，因此即使恢复设置为SI... 查看详情

SQL Server 数据库事务日志文件大小急剧增加

】SQLServer数据库事务日志文件大小急剧增加【英文标题】：SQLServerDatabaseTransactionLogFilesizeincreaseddramatically【发布时间】：2016-09-0519:46:46【问题描述】：备份后，我注意到SQLServer数据库事务日志文件的大小急剧增加。我们有10GB的... 查看详情

如何查看 SQL Server 2005 事务日志文件

】如何查看SQLServer2005事务日志文件【英文标题】：HowcanIviewSQLServer2005Transactionlogfile【发布时间】：2011-05-0809:35:29【问题描述】：如何查看SQLServer事务日志文件(.trn)？我希望能够在trn文件中查看各个交易。一些记录已从数据库中... 查看详情

从损坏的 SQL Server 数据库中恢复事务日志

】从损坏的SQLServer数据库中恢复事务日志【英文标题】：RecoveringtransactionlogfromcorruptSQLServerdatabase【发布时间】：2011-01-2901:35:37【问题描述】：我们有一个每周以简单模式备份的数据库。昨天，我们有一个crc错误损坏了mdf文件，... 查看详情

即席只读查询是不是存储在 SQL Server 事务日志中？

】即席只读查询是不是存储在SQLServer事务日志中？【英文标题】：Aread-hocread-onlyqueriesstoredinSQLServertransactionlog?即席只读查询是否存储在SQLServer事务日志中？【发布时间】：2011-07-2720:17:14【问题描述】：在数据库恢复模式配置为ful... 查看详情

SQL Server 2005 - 如何找出已恢复的事务日志文件

】SQLServer2005-如何找出已恢复的事务日志文件【英文标题】：SQLServer2005-Howtofindoutwhattransactionlogfileshavebeenrestored【发布时间】：2011-02-0215:11:08【问题描述】：您如何知道在SQLServer2005中使用SQL恢复了哪些事务日志备份文件？【问题... 查看详情

SQL Server：如何查询上次事务日志备份的时间？

】SQLServer：如何查询上次事务日志备份的时间？【英文标题】：SQLServer:howtoquerywhenthelasttransactionlogbackuphasbeentaken?【发布时间】：2011-04-1704:00:13【问题描述】：我想查询所有数据库（在SQLServer2008实例中）的最后一次事务日志备份... 查看详情

Sql Server 事务日志阅读器

】SqlServer事务日志阅读器【英文标题】：SqlServerTransactionLogReader【发布时间】：2012-08-0805:55:57【问题描述】：是否可以使用某种类型的流读取器对象打开sqlserver事务日志文件？我在C#中尝试了多种方法，但数据库似乎正在使用显... 查看详情

SQL Server：如何增加事务日志的大小？

】SQLServer：如何增加事务日志的大小？【英文标题】：SQLServer:HowdoIincreasethesizeofthetransactionlog?【发布时间】：2010-11-0322:08:14【问题描述】：如何增加事务日志的大小？是否也可以临时增加事务日志？假设我有以下情况。我的删除... 查看详情

如何清除 SQL Server 事务日志？

】如何清除SQLServer事务日志？【英文标题】：HowdoyoucleartheSQLServertransactionlog?【发布时间】：2010-09-0813:40:11【问题描述】：我不是SQL专家，每次我需要做一些超出基础的事情时，我都会想起这样一个事实。我有一个规模不大的测... 查看详情

SQL 从事务日志中恢复数据

...复数据？非常感谢【问题讨论】：【参考方案1】：使用完整备份和事务日志集合，您可以将数据库恢复到某个时间点。如果您不希望数据库脱​​机进行还原，则需要 查看详情

查看 SQL Server 2008 中的所有事务日志

】查看SQLServer2008中的所有事务日志【英文标题】：ViewalltransactionlogsinSQLServer2008【发布时间】：2010-11-1515:37:39【问题描述】：问题是这样的：t0:Insertismadeintomydatabaset1:Deleteisexecutedt2:Fullbackupismadet3:Transactionlogbackupismade如何在t3（现... 查看详情

SQL Server 2005 事务日志条目：LOP_Format_Page

】SQLServer2005事务日志条目：LOP_Format_Page【英文标题】：SQLServer2005TransactionLogEntry:LOP_Format_Page【发布时间】：2010-12-1804:01:10【问题描述】：我正在调查与ETL过程中的大型日志扩展相关的问题，即使数据库设置为批量记录模式（并... 查看详情

解决 SQL Server 事务日志填满磁盘空间的最佳方法是啥？

】解决SQLServer事务日志填满磁盘空间的最佳方法是啥？【英文标题】：WhatisthebestpossiblewaytotackleSQLServertransactionlogsfillingupdiskspace?解决SQLServer事务日志填满磁盘空间的最佳方法是什么？【发布时间】：2013-03-2000:04:36【问题描述】：... 查看详情