473,705 Members | 5,438 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

One Command Keeps Causing "'tempdb' is full" Error

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 uniqueidentifie r 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.BatchJournal ID, 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.BatchJournal ID 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
Jul 20 '05 #1
10 13270

"Jay Chan" <ja******@hotma il.com> wrote in message
news:c7******** *************** ***@posting.goo gle.com...
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.)

Jul 20 '05 #2
> 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
Jul 20 '05 #3

"Jay Chan" <ja******@hotma il.com> wrote in message
news:c7******** *************** **@posting.goog le.com...
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 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.
The new server was probably installed with a different default collation.
This is what bit us.


Thanks.

Jay Chan

Jul 20 '05 #4
> The new server was probably installed with a different default collation.
This is what bit us.


I will go look for it. Thanks for the pointer.

Jay Chan
Jul 20 '05 #5
> > How do you get the right collation when you rebuild your database?

Look up the syntax for CREATE DATABASE.
I cannot find the collation in the current database. I have a feeling
that it may not have anything to do with the problem that I am having
(see below).
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.)


I have greatly simplified the query for the purpose of testing, and I
still get the same error (and I have done a "dump transaction tempdb
with no_log" before I start the test). Because the query is very
simple, I don't think collation has to do with this problem. Moreover
the source table is quite small (3500 rows, each rows is around 34
bytes or a bit more). The whole source table can easily fit inside
tempdb with plenty of space to spare. And I have enlarged tempdb from
10MB to 200MB. But these don't help. I still have the problem. The
following is the simple SQL query that I am talking about:

create table #NewBatOp
(
OperationNum varchar(3) null
)
-- |-- Comment this one line
-- | out will not trigger
insert into #NewBatOp -- <---| the error
select opno from batop

drop table #NewBatOp

Basically, this simple query copies one field from all the rows in the
source table "batop" to a temporary table "#NewBatOp" . There is really
nothing complicated about this query.

Does anyone know anything about this problem?

Thanks for any info.

Jay Chan
Jul 20 '05 #6

"Jay Chan" <ja******@hotma il.com> wrote in message
news:c7******** *************** ***@posting.goo gle.com...
Finally, we have fixed the problem after we have contacted Microsoft
Tech Support.

We need to auto-grow the transaction-log of tempdb by "10MB" instead
of by "10%". If I understand this correctly, the transaction log of
tempdb was initially set to 1MB (somehow I cannot change it through
Enterprise Manager if I set it to grow by 10%), it would grow to only
1.1MB after it had grown by 10%. If the transaction log caused by the
INSERT statement is over 1.1MB, the database server will give out that
"Log file is full" message. Growing it by 10MB fixes the problem.

Before I changed it to grow by 10MB, I could not change the initial
size of the transaction log file of tempdb. It got stuck at 1MB.
Strangely, after I changed it to grow by 10MB, I can change the
initial size.


Note the 10% thing can be a bad thing for another reason. Overtime a 100MB
db can expand to say be 1 Gig... a 10% expansion will then be 100MB and will
take time (blocking inserts, etc) AND be more likely to fill up the disk or
fail because it can't allocate 10%. (perhaps a better example is 10Gig on a
10.5 gig disk. :-)

So I tend to always grow my DB's by a fixed amount.

Jul 20 '05 #7

"Erland Sommarskog" <so****@algonet .se> wrote in message
news:Xn******** **************@ 127.0.0.1...
Jay Chan (ja******@hotma il.com) writes:
Moreover, according to the tech support, that "Log file is full"
message is not an error message; that is just a warning message. I
could have changed my script to ignore that warning message, and I
would still be fine. I don't know if this is really true or not. But I
will not risk this.
Warning message? It has severity 17, so that is not a warning. Then
again, SQL Server and error handling.


I believe the order of events is a log file is full, which generates the
error and THEN an expansion of the DB. Which means the next insert may be
fine.

Sorta weird, but basically what I've seen.

In any case, I'm glad to hear that your case was resolved, and it was
interesting to learn how it was resolved.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Jul 20 '05 #8
> I believe the order of events is a log file is full, which generates the
error and THEN an expansion of the DB. Which means the next insert may be
fine.


If I understand yours correctly, this means the current INSERT failed,
and the next INSERT may be OK. This surely sounds like an error to me
(not a warning as what the MS tech support had suggested). In any
case, I don't know if this is true or not because I am not going to
re-create the problem and find this out (for lack of curiosity).

Thanks for the explanation on error-vs-warning.

Jay Chan
Jul 20 '05 #9
> When I read this I thought I maybe had learnt something new. Or maybe
there was some misunderstandin g on the way.

In any case, I ran this on my server at home:

select a.*, c.* into #explosion from
Northwind..Orde rs a, Northwind..Orde rs b, pubs..authors c
where 1 = 0
go
exec sp_helpdb tempdb
go
insert #explosion
select a.*, c.* from
Northwind..Orde rs a, Northwind..Orde rs b, pubs..authors c
go

The initial size of tempdb was 25 MB, with 5120 KB for the log.
When I killed the process from another window, the log for tempdb
had grown to over 1 GB, so apparently the log can grow more than
once during a statement.
I assume the purpose of the test is to see if one INSERT statement can
cause the log file of tempdb to grow more than once.

But your test script involves three separated steps (three different
'go' statements). Each one of those three steps can cause the log file
of the tempdb to expand. You may need to remove those 'go' statements
and re-test the script.

I don't mean to sound like I know this stuff. Actually, I don't really
know. I am just trying to follow the logic of the test.
My guess why growing 10 MB at a time worked, is that even if you
started with a small log, you eventually come to the situation when
10% was more than the remaining free space on the disk. By growing
10 MB at a time, you will not run out of disk, until you run out of
disk if you understand what I mean.


I think you are right to say that growing by 10MB instead of 10% seems
to be a safe way to grow the log file -- just in case it grows so big
that the disk is full. Thanks.

I need to point out that the problem that caused me to start this
message thread has nothing to do with disk becoming full. I need to
clarify this just in case someone jumps into the middle of this
message thread and misunderstands what this message thread was all
about.

Jay Chan
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
7437
by: Vineet Bisht | last post by:
Dear Sir I have problem which I am describing below:- In my databse , size of Data file is 40 MB size of Log file is 4158 MB which is causing the size of my database so huge why this log file is so huge ?
6
3037
by: Fan Ruo Xin | last post by:
Last monday, I tried to create a working table, and failed because of 'Log Full'. There were two applications running at that time - one is autoload (during the split phase), another one is "insert into a (NLI) ... SELECT ... FROM ...". I checked the db2diag.log (level=4) and notification file. It was interesting that there was no any information about log full. I had to kill the autoload process. I haven'g got time to see if I can...
0
1892
by: Rob | last post by:
I need to allow a user with the default "Full Data User" permissions on the front-end database to relink to a table in a backend to which he has the same permissions. (I'd like to do read, update, insert, delete, which are the DEFAULTS for a "Full data user", but just so you know I have also added "Administrator" for this group in front and backends, just to try and figure out the problem and it didn't help). Anyway I cycle through the...
26
2992
by: Michel Rouzic | last post by:
I have a binary file used to store the values of variables in order to use them again. I easily know whether the file exists or not, but the problem is, in case the program has been earlier interupted before it could write the variables to the file, the file is gonna be empty, and then it's gonna load a load of crap into variables, which i want to avoid. That file is always 36 bytes big (it contains 4 double-precision floats and one...
11
2442
by: VJ | last post by:
I have these functions in my application. FileInfo.CopyTo DirectoryInfo.Create File.Copy Directory.Create
0
959
by: gregory_may | last post by:
I may be having an issue with sending too much information out the network via async udp. Is there a way I can check to see if the network adapter is full/sending stuff already? As I keep sending, the latency grows from miliseconds to 10's of whole seconds. I am sending full 64K fraims in each send. Dim MyClient System.Net.Sockets.UdpClient myClient.Send(MyPayload, BytesToSend, EndPoint)
0
1168
by: danaelifaz | last post by:
Hi, We are thinking of compiling our project in Full Optimization (/Ox) mode. I understand it may cause problems / there are bugs in this feature. Did any of you ever used that and can share the experiance? can you recommend of some reading material? Thanks!
1
1306
by: ShivBahadur | last post by:
Hi All, how to remove "tempdb is full error in sqlserver2000"
1
3359
by: jan.marien | last post by:
we have a table with jobs and a table with job_history information. Users can define jobs and let them run every X minutes/hours , like a cronjob. The jobs table has the following trigger: CREATE TRIGGER JOBS_AFTER_DELETE AFTER DELETE ON JOBS REFERENCING OLD AS o FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
0
8768
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8690
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9139
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7895
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6606
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4440
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4704
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2491
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2083
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.