一个命令保持导致“'tempdb'已满”错误 [英] One Command Keeps Causing "'tempdb' is full" Error
问题描述
当我运行一系列SQL时，我不断收到以下错误消息
命令：
服务器：Msg 9002，Level 17，State 6，第15行
数据库''tempdb''的日志文件已满。
备份数据库的事务日志
免费一些日志空间。
我试过dump transaction tempdb with no_log就在我运行之前
SQL命令。但这没有用。
我尝试运行的SQL命令系列如下：
create table #NewBatOp
（
BatchJournalID uniqueidentifier not null，
batch_nr varchar（5）null，
OperationNum varchar（3） null，
OperationHours真空，
EmployeeNum varchar（6）null，
OperationDate datetime null，
IsOverTime tinyint null
）
- | - 评论这一行
- | out不会触发
插入#NewBatOp - < --- |错误
选择
bj.BatchJournalID，bj.batch_nr，bo.opno，
bo.hrs，bo.bonno，bo.dat ，bo.otflg
来自batop bo
内部加入BatchJournal bj on
bo.bat = bj.batch_nr和
bj.BatchJournalID在
（从BatchControl中选择BatchJournalID）
if（@@ error<> 0）
goto OnError
drop table #NewBatOp
转到EndTest
OnError：
drop table＃ NewBatOp
print"错误：无法将新的批处理操作导入
日记。"
EndTest：
我试过在ISQL和Query
Analyzer中运行上面的语句，我也得到了同样的错误。
在将数据库从一台服务器移动到另一台服务器之前，我没有遇到这个问题。
- 旧服务器中的操作系统是Windows-NT，
和旧服务器中的SQL Server是2000版本。
- 新服务器中的操作系统是Windows-2000，
和旧服务器中的SQL Server是2000版本。
两个服务器中tempdb的设置大致相同。
实际上，新服务器中的tempdb实际上比
大得多旧服务器。
服务器中的事务日志大小是相同的（并且不能手动更改）。数据
和tempdb的事务日志都可以自动增长10％
增量，不受大小限制。
tempdb的数据和日志都在一个硬盘中。硬盘
来自选择设置。上面的语句只有530KB（结果集中大约3000行
）。我相信这是一个非常小的数据库操作。
因此，我认为这个大小与错误无关。
我不喜欢不要认为内部联合条款是问题的原因。
的原因是我使用了相同的内部联接。在其他
查询中的条款，他们没有任何问题。事实上，我已经使用了许多其他查询，这些查询要复杂得多，并且在tempdb中创建了更大的结果集，并且他们没有这个问题。
我对此错误感到非常困惑。有人可以给我一个指针吗？
提前感谢任何信息。
Jay Chan
" Jay Chan" < JA ****** @ hotmail.com>在留言中写道
news：c7 ************************** @ posting.google.c om ...当我运行一系列SQL
命令时，我不断收到以下错误消息：
服务器：Msg 9002，Level 17，State 6，Line 15
数据库tempdb的日志文件已满。
备份数据库的事务日志
释放一些日志空间。
我试过了dump transaction tempdb with no_log就在我运行SQL命令之前。但这没有用。
要检查的是所涉及的表格的整理。
我们有这个问题发生在我们的一个系统上，直到我们用正确的整理重建
数据库，我们才会遇到同样的问题。
基本上我认为在我们的案例中发生的是整个来源
表将被复制到tempdb然后转换表。匹配
整理，然后进行连接。 （因为你不能做一个准确的
加入不匹配的校对，因为你必须确定例如
''Joe''匹配''JOE'' 。取决于可能匹配或不匹配的归类。）
>要检查的一件事是所涉及的表的校对。
我会调查这个。
我们遇到了这个问题我们的系统，直到我们用正确的整理重建
数据库，我们都会遇到同样的问题。
如何在重建数据库时获得正确的排序规则？
基本上我认为在我们的案例中发生的事情是整个来源
将表复制到tempdb中，然后转换表格。匹配
整理，然后执行连接。 （因为你不能准确地加入一个不匹配的校对，因为你必须确定例如'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''可能不匹配。）
如果是这种情况，我可能需要更改查询以减少
中间临时表的数量查询生成。
这是非常奇怪的，因为相同的SQL语句工作正常
我将数据库移动到新服务器。我会看到这是怎么回事。
谢谢。
Jay Chan
" Jay Chan" < JA ****** @ hotmail.com>在消息中写道
新闻：c7 ************************* @ posting.google.co m ... < blockquote class =post_quotes>要检查的是所涉及的表的整理。
我会调查这个。
我们我们的一个系统出现了这个问题，直到我们用正确的整理重建了数据库，我们才会遇到同样的问题。
你如何做到正确重建数据库时整理？
查找CREATE DATABASE的语法。
还有一种方法（我总是忘记）以编程方式获得服务器的
默认排序规则类型。
基本上我认为在我们的案例中发生的事情是整个来源将表格复制到tempdb中，然后转换表格。到
匹配排序规则，然后执行连接。 （因为如果''Joe''匹配''JOE''，你必须确定例如
，因为你不能准确地加入不匹配的校对。取决于可能的排序或者可能不会
匹配。）
如果是这种情况，我可能需要更改查询以减少查询生成的中间临时表的数量。
这很奇怪，因为在将数据库移动到新服务器之前，相同的SQL语句工作正常。我会看到这是怎么回事。
新服务器可能安装了不同的默认排序规则。
这就是我们的位置。
谢谢。
Jay Chan
I keep getting the following error message when I run a serie of SQL
commands:
Server: Msg 9002, Level 17, State 6, Line 15
The log file for database ''tempdb'' is full.
Back up the transaction log for the database
to free up some log space.
I have tried "dump transaction tempdb with no_log" right before I run
the SQL command. But that doesn''t help.
The serie of SQL commands that I try to run is the following:
create table #NewBatOp
(
BatchJournalID uniqueidentifier not null,
batch_nr varchar(5) null,
OperationNum varchar(3) null,
OperationHours real null,
EmployeeNum varchar(6) null,
OperationDate datetime null,
IsOverTime tinyint null
)
-- |-- Comment this one line
-- | out will not trigger
insert into #NewBatOp -- <---| the error
select
bj.BatchJournalID, bj.batch_nr, bo.opno,
bo.hrs, bo.bonno, bo.dat, bo.otflg
from batop bo
inner join BatchJournal bj on
bo.bat = bj.batch_nr and
bj.BatchJournalID in
(select BatchJournalID from BatchControl)
if ( @@error <> 0 )
goto OnError
drop table #NewBatOp
goto EndTest
OnError:
drop table #NewBatOp
print "Error: Failed to import new batch-operations into
journal."
EndTest:
I have tried running the above statements in ISQL and in Query
Analyzer, and I get the same error.
I didn''t have this problem before I have moved the database from one
server to another server.
- The OS in the old server is Windows-NT,
and the SQL Server in the old server is the 2000 version.
- The OS in the new server is Windows-2000,
and the SQL Server in the old server is the 2000 version.
The settings in tempdb in both servers are more or less the same.
Actually, the tempdb in the new server is actually much bigger than
the one in the old server. The size of the transaction logs in both
server are the same (and cannot be changed manually). Both the data
and the transaction log of tempdb can automatically grow in 10%
increment and no restriction on size.
The data-and-log of the tempdb are both in one hard disk. The hard
disk has 10-GB free space available. Moreover the size of the result
set from the "select" statement above is only 530KB (around 3000 rows
in the result-set). I believe it is a very small database operation.
Therefore, I don''t think the size has anything to do with the error.
I don''t think the "inner-join" clause is the cause of the problem. The
reason is that I have used the same "inner-join" clause in other
queries, and they don''t have any problem. As a matter of fact, I have
used many other queries that are far more complicated and have created
much bigger result set in tempdb, and they don''t have this problem.
I am very puzzled of this error. Can someone give me a pointer?
Thanks in advance for any info.
Jay Chan
"Jay Chan" <ja******@hotmail.com> wrote in message
news:c7**************************@posting.google.c om...I keep getting the following error message when I run a serie of SQL
commands:
Server: Msg 9002, Level 17, State 6, Line 15
The log file for database ''tempdb'' is full.
Back up the transaction log for the database
to free up some log space.
I have tried "dump transaction tempdb with no_log" right before I run
the SQL command. But that doesn''t help.
One thing to check is the Collation of the tables involved.
We had this problem occur on one of our systems and until we rebuilt the
database with the right collation we''d have the same problem.
Essentially I think what was happening in our case was the ENTIRE source
tables would be copied into the tempdb and then "converted" to a matching
collation, and then the join performed. (Since you can''t do an accurate
join on a mismatched collation since you have to determine for example if
''Joe'' matches ''JOE''. Depending on the collation that may or may not match.)
> One thing to check is the Collation of the tables involved.
I will look into this.
We had this problem occur on one of our systems and until we rebuilt the
database with the right collation we''d have the same problem.
How do you get the right collation when you rebuild your database?
Essentially I think what was happening in our case was the ENTIRE source
tables would be copied into the tempdb and then "converted" to a matching
collation, and then the join performed. (Since you can''t do an accurate
join on a mismatched collation since you have to determine for example if
''Joe'' matches ''JOE''. Depending on the collation that may or may not match.)
If this is the case, I may need to change the query to reduce the
number of intermediate temporary tables that the query generates.
This is very strange because the same SQL statement worked fine before
I move the database to the new server. I will see how this goes.
Thanks.
Jay Chan
"Jay Chan" <ja******@hotmail.com> wrote in message
news:c7*************************@posting.google.co m...One thing to check is the Collation of the tables involved.
I will look into this.We had this problem occur on one of our systems and until we rebuilt the
database with the right collation we''d have the same problem.
How do you get the right collation when you rebuild your database?
Look up the syntax for CREATE DATABASE.
There''s also a way (which I ALWAYS forget) to programatically get the
default collation type of the server.
Essentially I think what was happening in our case was the ENTIRE source
tables would be copied into the tempdb and then "converted" to a matching collation, and then the join performed. (Since you can''t do an accurate
join on a mismatched collation since you have to determine for example if ''Joe'' matches ''JOE''. Depending on the collation that may or may notmatch.)
If this is the case, I may need to change the query to reduce the
number of intermediate temporary tables that the query generates.
This is very strange because the same SQL statement worked fine before
I move the database to the new server. I will see how this goes.
The new server was probably installed with a different default collation.
This is what bit us.
Thanks.
Jay Chan
这篇关于一个命令保持导致“'tempdb'已满”错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
