473,320 Members | 1,961 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQL Server 2000 - Server: Msg 9002, Level 17, State 6, Line 1

I have a stored procedure that takes 18 hours to complete. Sometimes
I get the below error message when I run the stored procedure.

'Server : Msg 9002, Level 17, State 6, Line 1 The log file for
database 'Customer' is full. Back up the transaction log for the
database to free up some log space.'.

I checked my log file and it is not full. And I don't know why I am
getting this message. Any help is appreciated.

Thanks,
Anjula
Jul 20 '05 #1
2 13011
Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...
[posted and mailed, please reply in news]

Anjula (an********@hotmail.com) writes:
I have a stored procedure that takes 18 hours to complete. Sometimes
I get the below error message when I run the stored procedure.

'Server : Msg 9002, Level 17, State 6, Line 1 The log file for
database 'Customer' is full. Back up the transaction log for the
database to free up some log space.'.

I checked my log file and it is not full. And I don't know why I am
getting this message. Any help is appreciated.


You probably get this message because your transaction log has filled
up, and there is no room on the disk for the file to autogrow.

Am I right to assume that the stored procedure performs updates?

It seems to me that the procedure has a very long transaction running.
When SQL Server truncates the transaction log - which it does spontaneously
if you are running with simple recovery - it can never truncate any
further ahead that the oldest open transaction.

If the procedure performs a whole lot of updates in one long transaction,
it will eventually fill up the log. But once the transaction has been
rolled back, SQL Server can truncate the log, and will do if you are
running with simple recovery.


Hi Erl,

I am doing updates in my stored procedure. I have one update in one
transaction.
I checked my transaction log space before I ran this proc and I had
enough room for it to grow. Sometimes this proc runs fine and
sometimes I get the above error.

Thanks,
Anjula
Jul 20 '05 #2
Anjula (an********@hotmail.com) writes:
I am doing updates in my stored procedure. I have one update in one
transaction.
I checked my transaction log space before I ran this proc and I had
enough room for it to grow. Sometimes this proc runs fine and
sometimes I get the above error.


Apparently there was not enough with space for it to grow. I would
expect that a procedure that runs for 18 hours incurs quite a strain
on the transaction log. Note also that other updates that takes place
while the procedure is running will also take up space in the log.

Not knowing anything about your application, I can only give general
advice such as investigate whether you can break up the transaction in
smaller parts without violating data integrity.

Also, you should investigate which recovery mode you are using, and
if you are running with full or bulk-logged mode, if you can increase
how often you back up the transaction log.
--
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 #3

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

Similar topics

2
by: Ken Lindner | last post by:
I have a need to become familiar with SQL Server 2000 for work. Needless to say I am new to SQL Server any version, but not IT in general. My employer has provided me with the SQL Server 2000...
1
by: ErickR | last post by:
We are experiencing a problem with Sql Server 2000 linking to an Access 97 file. We have two machines that link to this .mdb file, and we recently upgraded one to newer hardware, SP3a, MDAC 2.8,...
2
by: jet | last post by:
Hi, Maybe this is an easy task, but I'm having a really hard time figuring out how to do this. I'm a complete newbie to SQL Server. I have a database dump file from MySQL that's in .sql...
3
by: markydev | last post by:
Hi, I'm using sql server 2000 sp4. I've 2 databases linked, an instance and my local. I'm getting two different errors when trying to update the remote table (local server) from the instance....
2
by: Amanda | last post by:
From a guy in Microsoft newsgroups: | In *comp.databases.ibm-db2* there are always IBM guys | from the Toronto labs on line.Post with the | -for the love of god please help- | line...
3
by: Branco Medeiros | last post by:
Dear (and mighty) all: I backed up a database (SQL server 7.0) and tried to restore it on another system (SQL Server 2000). This is not the first time I'm doing this and never had a problem...
2
by: Jobs | last post by:
Download the JAVA , .NET and SQL Server interview with answers Download the JAVA , .NET and SQL Server interview sheet and rate yourself. This will help you judge yourself are you really worth of...
4
by: lcalabro | last post by:
Server: Msg 9002, Level 17, State 6, Procedure DelTDByTrxNum, Line 83 The log file for database 'agresso' is full. Back up the transaction log for the database to free up some log space. How do...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.