Although my query is correct, I will intermittently get this error. Is there a way to avoid writing to transaction log?
尽管我的查询是正确的，但我将间歇性地获得这个错误。是否有一种方法可以避免写入事务日志?
When I checked the specified column, each column's text read: "Nothing"
当我检查指定的列时，每个列的文本都是:“没什么”
The Error:
错误:
The transaction log for database 'tempdb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases".
数据库“tempdb”的事务日志已满。要了解日志中的空间为什么不能重用，请参阅system .databases中的log_reuse_wait_desc列”。
Thanks, greatly appreciated, somewhat urgent.
谢谢，非常感谢，有点紧急。
4 个解决方案
#1
4
You need to use the transaction log. It's how SQL rolls back in case of an error or partial completion of a query.
您需要使用事务日志。当查询出现错误或部分完成时，SQL就是这样回滚的。
The root cause is not the transaction log, it's the query. You either:
根本原因不是事务日志，而是查询。你:
1 - Have a poorly optimized query (lots of sorts or hash joins can cause this)
1 -有一个优化不佳的查询(大量的排序或散列连接会导致这种情况)
2 - Are overusing #temp tables in your query
2 -在查询中过度使用#temp表
3 - Have a drive space issue
3 -存在驱动器空间问题
Can you post the query you are running?
您可以发布正在运行的查询吗?
EDIT:
编辑:
To clarify what could be causing the issue...
要弄清楚是什么原因导致了这个问题……
If you have multiple #temp tables, they all take up space in tempdb. Any sorting that the query engine has to do
(ORDER BY without an index, complicated JOIN conditions that don't use indexes, lots of GROUP BY, or aggregate functions, etc)
writes to the transaction log for tempdb because all that sorting is done there.
如果您有多个#temp表，它们都会占用tempdb中的空间。查询引擎必须进行的任何排序(不使用索引的排序、不使用索引的复杂连接条件、大量的组BY或聚合函数等)都写入到tempdb的事务日志中，因为所有的排序都是在那里完成的。
The query itself may function fine but it probably needs to be optimized to avoid these issues.
查询本身可能功能良好，但它可能需要进行优化以避免这些问题。
#2
1
No, EVERY transaction gets logged. However, this is not a problem with logging.
不，每个事务都被记录。但是，这不是日志记录的问题。
It states the tempdb is full, not your log file. Are you processing a lot of records? Using temp table?
它声明tempdb已满，而不是您的日志文件。你正在处理大量的记录吗?使用临时表?
You need to manage the growth in the sense that you may need to do batches and commit more often.
您需要管理增长，因为您可能需要进行批量操作并更频繁地提交。
#3
0
SQL Server uses transaction logs to keep track of what its doing, there is no way to avoid this (nor would you want to, for many, many reasons). The issue is here is this:
SQL Server使用事务日志来跟踪其行为，无法避免这种情况(出于许多原因，您也不希望这样做)。问题是:
-
temdb is a system DB in sql server that sql server uses to write temp data, such as cached data for really long queries, and temp tables (any tables created with #, ## as the prefix).
temdb是sql server中的一个系统DB, sql server使用它来编写临时数据，例如用于长时间查询的缓存数据和临时表(以#、##为前缀创建的任何表)。
-
when you first installed the sql server, it created the tempdb on the drive all the data is on.
当您第一次安装sql服务器时，它在驱动器上创建了所有数据都在运行的tempdb。
-
when you run a query that does temp tables, or is long running, the cached data is written in tempdb - when it does that, it also inherently uses the transaction log of the tempdb.
当您运行一个执行temp表的查询，或者是长期运行时，缓存的数据是在tempdb中编写的—当它这样做时，它也会从本质上使用tempdb的事务日志。
In your case, the log file of the DB gets filled up probably because there is too much data being cached/written to tempdb, and the drive that tempdb log file is probably full. You should have 'shrink automatically' option 'on' for your tempdb, and to fix this problem easily, you can restart sql services, and it will automatically eliminate the extra data in the log file for tempdb.
在您的例子中，DB的日志文件可能会被填满，因为有太多的数据被缓存/写入到tempdb，而且tempdb日志文件的驱动器可能已经满了。您应该为您的tempdb设置“自动收缩”选项，为了方便地解决这个问题，您可以重新启动sql服务，它将自动消除tempdb日志文件中的额外数据。
You could try to tune your query a little bit, so it doesn't have to write too much data to the tempdb (eliminate long result sets. avoid #temptables, etc)
您可以尝试调优您的查询，这样它就不必向tempdb写入太多数据(消除长结果集)。避免#可诱惑的等)
#4
0
Just to validate the tempdb problem, in your JOINS, force LOOP join ex: select * from test inner LOOP join test2 on x=y
为了验证tempdb问题，在您的连接中，强制循环连接ex: select * from test inner LOOP join test2 on x=y
AND remove all ORDER BY clauses.
并按条款删除所有订单。
If the query runs without filling your tempdb (but may be a lot slower) then you know that hash (or sorting) is required and using too much tempdb space.
如果查询运行时没有填充tempdb(但速度可能要慢得多)，那么您就知道需要散列(或排序)，并且使用了太多的tempdb空间。
It may requires good query analysis to fix that, but check the execution plan and try to do everything possible to remove any sorting or hash join, if possible by creating index(es) or sometimes forcing join type (but normally if statistics are good you should do that only as the last possibility).
它可能需要良好的查询分析为了解决这个问题,但检查执行计划和努力尽一切可能删除任何排序或散列连接,如果可能的话,通过创建索引(es)或有时迫使连接类型(但通常如果统计好你应该做的,只有最后一种可能性)。
Good luck
祝你好运
#1
4
You need to use the transaction log. It's how SQL rolls back in case of an error or partial completion of a query.
您需要使用事务日志。当查询出现错误或部分完成时，SQL就是这样回滚的。
The root cause is not the transaction log, it's the query. You either:
根本原因不是事务日志，而是查询。你:
1 - Have a poorly optimized query (lots of sorts or hash joins can cause this)
1 -有一个优化不佳的查询(大量的排序或散列连接会导致这种情况)
2 - Are overusing #temp tables in your query
2 -在查询中过度使用#temp表
3 - Have a drive space issue
3 -存在驱动器空间问题
Can you post the query you are running?
您可以发布正在运行的查询吗?
EDIT:
编辑:
To clarify what could be causing the issue...
要弄清楚是什么原因导致了这个问题……
If you have multiple #temp tables, they all take up space in tempdb. Any sorting that the query engine has to do
(ORDER BY without an index, complicated JOIN conditions that don't use indexes, lots of GROUP BY, or aggregate functions, etc)
writes to the transaction log for tempdb because all that sorting is done there.
如果您有多个#temp表，它们都会占用tempdb中的空间。查询引擎必须进行的任何排序(不使用索引的排序、不使用索引的复杂连接条件、大量的组BY或聚合函数等)都写入到tempdb的事务日志中，因为所有的排序都是在那里完成的。
The query itself may function fine but it probably needs to be optimized to avoid these issues.
查询本身可能功能良好，但它可能需要进行优化以避免这些问题。
#2
1
No, EVERY transaction gets logged. However, this is not a problem with logging.
不，每个事务都被记录。但是，这不是日志记录的问题。
It states the tempdb is full, not your log file. Are you processing a lot of records? Using temp table?
它声明tempdb已满，而不是您的日志文件。你正在处理大量的记录吗?使用临时表?
You need to manage the growth in the sense that you may need to do batches and commit more often.
您需要管理增长，因为您可能需要进行批量操作并更频繁地提交。
#3
0
SQL Server uses transaction logs to keep track of what its doing, there is no way to avoid this (nor would you want to, for many, many reasons). The issue is here is this:
SQL Server使用事务日志来跟踪其行为，无法避免这种情况(出于许多原因，您也不希望这样做)。问题是:
-
temdb is a system DB in sql server that sql server uses to write temp data, such as cached data for really long queries, and temp tables (any tables created with #, ## as the prefix).
temdb是sql server中的一个系统DB, sql server使用它来编写临时数据，例如用于长时间查询的缓存数据和临时表(以#、##为前缀创建的任何表)。
-
when you first installed the sql server, it created the tempdb on the drive all the data is on.
当您第一次安装sql服务器时，它在驱动器上创建了所有数据都在运行的tempdb。
-
when you run a query that does temp tables, or is long running, the cached data is written in tempdb - when it does that, it also inherently uses the transaction log of the tempdb.
当您运行一个执行temp表的查询，或者是长期运行时，缓存的数据是在tempdb中编写的—当它这样做时，它也会从本质上使用tempdb的事务日志。
In your case, the log file of the DB gets filled up probably because there is too much data being cached/written to tempdb, and the drive that tempdb log file is probably full. You should have 'shrink automatically' option 'on' for your tempdb, and to fix this problem easily, you can restart sql services, and it will automatically eliminate the extra data in the log file for tempdb.
在您的例子中，DB的日志文件可能会被填满，因为有太多的数据被缓存/写入到tempdb，而且tempdb日志文件的驱动器可能已经满了。您应该为您的tempdb设置“自动收缩”选项，为了方便地解决这个问题，您可以重新启动sql服务，它将自动消除tempdb日志文件中的额外数据。
You could try to tune your query a little bit, so it doesn't have to write too much data to the tempdb (eliminate long result sets. avoid #temptables, etc)
您可以尝试调优您的查询，这样它就不必向tempdb写入太多数据(消除长结果集)。避免#可诱惑的等)
#4
0
Just to validate the tempdb problem, in your JOINS, force LOOP join ex: select * from test inner LOOP join test2 on x=y
为了验证tempdb问题，在您的连接中，强制循环连接ex: select * from test inner LOOP join test2 on x=y
AND remove all ORDER BY clauses.
并按条款删除所有订单。
If the query runs without filling your tempdb (but may be a lot slower) then you know that hash (or sorting) is required and using too much tempdb space.
如果查询运行时没有填充tempdb(但速度可能要慢得多)，那么您就知道需要散列(或排序)，并且使用了太多的tempdb空间。
It may requires good query analysis to fix that, but check the execution plan and try to do everything possible to remove any sorting or hash join, if possible by creating index(es) or sometimes forcing join type (but normally if statistics are good you should do that only as the last possibility).
它可能需要良好的查询分析为了解决这个问题,但检查执行计划和努力尽一切可能删除任何排序或散列连接,如果可能的话,通过创建索引(es)或有时迫使连接类型(但通常如果统计好你应该做的,只有最后一种可能性)。
Good luck
祝你好运