By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,296 Members | 1,476 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,296 IT Pros & Developers. It's quick & easy.

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

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.