469,080 Members | 1,710 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,080 developers. It's quick & easy.

Why the transaction log file full

I have set the initial size of the log file for a database to 1M, the
maximum size is unrestricted, and the increase rate is 10%.

However, when I attempt to delete thousands of rows, the error is still
reported that the transaction log file is full. Why can't the log file
increase automatically?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #1
3 5045
Hi

You don't give the current size of the log file, but at a guess 10% of the
current log files size is larger than the free space on the disk or the
quotas allowed on it.

Log file filling up
http://www.support.microsoft.com/?id=110139

Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512

http://www.mssqlserver.com/faq/logs-shrinklog.asp
Log File Grows too big
http://www.support.microsoft.com/?id=317375
For log file shrinking etc.. check out :

http://msdn.microsoft.com/library/de...r_da2_1uzr.asp

and the following KB articles:

INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default...b;en-us;256650

INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default...b;en-us;272318

John

"Daniel Xiao" <xi*******@yahoo.com> wrote in message
news:40**********************@news.newsgroups.ws.. .
I have set the initial size of the log file for a database to 1M, the
maximum size is unrestricted, and the increase rate is 10%.

However, when I attempt to delete thousands of rows, the error is still
reported that the transaction log file is full. Why can't the log file
increase automatically?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

Jul 20 '05 #2
Daniel Xiao (xi*******@yahoo.com) writes:
I have set the initial size of the log file for a database to 1M, the
maximum size is unrestricted, and the increase rate is 10%.

However, when I attempt to delete thousands of rows, the error is still
reported that the transaction log file is full. Why can't the log file
increase automatically?


So what size is your transaction log now?

Even if the maximum size is unrestricted, you can still run out of disk
space. :-(. Say that you log is 1GB, and you have 50 MB free. Then you
will get this message, because there not room for 100 MB.

You may also consider to backup your transaction log to free some space.
Also, what requirements do you have in case of a crash? Is up-to-the
minute recovery essential to you?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3
Daniel Xiao <xi*******@yahoo.com> wrote in message news:<40**********************@news.newsgroups.ws> ...
I have set the initial size of the log file for a database to 1M, the
maximum size is unrestricted, and the increase rate is 10%.

However, when I attempt to delete thousands of rows, the error is still
reported that the transaction log file is full. Why can't the log file
increase automatically?
*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!


It's probably because the log can't grow quickly enough to keep pace
with the delete operation. The threshold which triggers the "grow
log" action is too close to the physical end of the log file because
the overall log size is so small initially. If you make the log a
decent size to begin with (say, 50Mb), the triggering threshold will
be proportionally further away from the physical end of the file, and
so the log should have time to grow before it actually fills up.
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by TZoner | last post: by
3 posts views Thread by Thiko | last post: by
3 posts views Thread by Bucfan1 | last post: by
5 posts views Thread by BashiraInTrouble | last post: by
2 posts views Thread by francois1 | last post: by
4 posts views Thread by yashgt | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.