January 15, 2003 at 8:26 am
Is there a limit to the number of times the log will auto grow to accomodate a single transaction?
In order to change the the primay key on a 4.3GB table, one of my developers decided to insert the records into a new table so he could ALTER the existing table and copy the data back.
This was a logged operation. The transaction log started out with about 3.5GB free in a 5.1GB log file. Log file properties are set to auto grow by 10% with unrestricted filesize.
The copy process started late last night and this morning I was greated with the following error message in our SQL accounting application "The log file for database 'SI' is full. Backup the transaction log for the database to free up some space."
After recovering from a small heart attack I did some investigation. While looking into the situation, the copy/alter table process finished and our normally scheduled first log backup of the day ran and things went back to normal.
The log file is now 515MB bigger than it was last when the process started. This is 10% larger. I don't know why it did not continue to grow if more space was needed. There is plenty of physical disk space available on the log drive.
The SQL Server log shows the log full errors being generated from 3:50am until the process finished at 6:45am. The log backup ran at 7:00am.
Any ideas?
Jeff451SQL Guru wannabe
January 15, 2003 at 9:51 am
Jeff,
Is it so that you are carrying out lot of processing in the tempdb database. I have seen this happen with us also. On analysing, i found that tempdb was growing largely at times to 10 GB, which might have eaten up the disk space.
January 15, 2003 at 9:53 am
Normally there is no limit of the numer of times a file file grow.
This error message means that you either run out of available disk space our you have reached the defined limit.
Some questions to find out your problem:
- How many log files do you have?
- Do you have other files on the same drive then the log(s)?
- How big is your log file and how big is the drive hosting your log file?
- Do you have the recovery mode set to simple?
- Do you have autoshrink set?
Because I could imagine that you run out of space, after your transaction has been rolled back, after some time your database/log has been shrinked.
To avoid the log growing you have to copy your table by parts and backup/trunc the log in between
Bye
Gabor
January 15, 2003 at 10:44 am
I agree with above. Likely the log grew and filled the disk. When the transaction rolled back, the log shrank back. Do you have autoshrink on?
I'd move the table in batches or parts as suggested above.
Steve Jones
January 15, 2003 at 2:15 pm
Thanks for the replies. Here are some answers.
We have only 1 log file for this database.
There are other files on the same drive as the log file (Yes I know this is bad - I inherited this server)
The log file is now 5.6GB - it is on a 32GB Drive with 8GB available.
This is SQL Server 7.0 and truncate log on check point is not checked.
Auto Shrink is not checked
What is strange is that the process completed even though we received the log full errors. The table has a create date of today at 6:40am and contains the correct number of rows. This transaction did not roll back.
I am wondering if the accounting application was generating the log full error messages even though the log did grow enough to accomodate the big insert?
Perhaps the accounting application was "blocked" from the log and interpreted this as a log full condition when in fact the log was just busy growing and logging the big insert? Seems unlikely but that sort of explains the behavior that I saw.
Jeff451SQL Guru wannabe
January 15, 2003 at 3:41 pm
Do have a maximum growth size set for the Log. If it reaches that the file will no longer grow and transaction will continue until it is full. If not then are you sure of what the free space was when this occurred and is tempdb on the same drive? If temodb is then it may have been involved in this overall.
January 16, 2003 at 6:55 am
The log files for the SI database was set to unrestricted filegrowth.
Tempdb is on a different drive with plenty of free space and it did not appear to grow during this issue.
It seems as if the big insert process caused the log file to grow once. This was then enough space to accomodate the transaction and it completed normally.
The Great Plains accounting application was the one with the error message on the screen. The SQL Server log showed error messages ocurring about every 10 seconds. This is error number 9002. This leads me to think that only the accounting application thought is was out of log space.
The accounting application was at the end of the posting process when the first error message occured. the last posting journal printed at 3:52am and the first error in the SQL Server log was at 3:53am.
A few seconds later the SQL Server log had the following message: Automatic checkpointing is disabled in database 'SI' because the log is out of space
This was followed by Could not write a CHECKPOINT record in database ID 9 because the log is out of space.
Database ID 9 is the SI database where the big insert and the posting was taking place.
In summary my questions remains these -
If the disk had space and the log file was set to autogrow why did it not keep growing?
If something prevented the log from growing and caused the error messages in the SQL Server log for 2 hours and 50 minutes why did the big insert transaction complete without rollback?
The SQL Server error messages in the log stopped when the big insert process completed not because I did anything to free up log space.
Jeff451SQL Guru wannabe
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply