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

[Anjula]

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

[Anjula]

Erland Sommarskog <so****@algonet .se> wrote in message news:<Xn******* *************** @127.0.0.1>...

[posted and mailed, please reply in news]

Anjula (an********@hot mail.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

[Erland Sommarskog]

Anjula (an********@hot mail.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