transaction log filling up errors

[jcaulder]

I have a recurring problem of a transaction log filling up and don't understand exactly what SQL Server is doing with the space allocation I specify.

I have the database set to 'simple' recovery model which means the transaction log truncates on checkpoints I thought? The database backs up once a day at 2:00am.

The transaction log was set to 'Restrict File Growth 10MB' and 'Space Allocated 2MB'. It is set to 'Automatically Grow File by 10%'.

When the transaction log first ran out of space, I decided to experiment and manually increased the 'Restrict File Growth to 100MB' and manually changed the 'Space Allocated to 15MB'.

The next time the transaction log filled up, I checked my transaction log settings and they had been changed to 'Restrict File Growth 31MB' and 'Space Allocated 2MB' even though I didn't change them.

Does SQL Server automatically alter these settings for some reason? If so, how can I prevent this? Because this problem keeps occuring, today I set the transaction log to 'Unrestricted File Growth' until I can resolve the issue.

It appears to report the log filled up when it bumps into the 2MB space allocated even though the file should be able to grow to 31MB. Am I reading this wrong? The space allocated is the files current space usage on disk of a total available to it specified in the restrict file growth setting right? Shouldn't the 'space allocated' grow until it reaches the maximum size specified in the 'restrict file growth' setting? And furthermore, shouldn't it drop every time the transaction log truncates due to checkpoints being issued. Is there something that prevents checkpoints from occurring regularly and this is blowing up the transaction log?

It is also puzzling to me at how the transaction log could be getting so large when the recovery model is set to 'simple'(related to checkpoint issue above perhaps?).

TIA

 

[tlbroadbent]

There are a number of reasons a transaction log may grow. even with Simple recovery, committed transactions will be deleted on checkpoint. Uncommitted transactions will remain on the log until the transaction completes. The file doesn't immediately shrink when the transactions are truncated even if the databse is set to Auto Shrink.

How big is the database? Do you have any large updates that run? Are you reindexing the databases regularly. Reindexing can cause transaction log growth.

SQL Server doesn't reset the file growth factor or maximum log size. At least I've never seen I do that.

I have seen SQL Server fail transactions with a log full message even when the log is set for unlimited growth. It appears to me that this occurs because SQL can;t grow the log file as fast as needed to keep up with the transactions for the databases. When this occurs, we set the log file initial size larger and the growth factor largerm, also. This minimizes the number of times SQL must grow the log to handle a current transaction.

SQL can also fail with a log full message if there isn't sufficient disk space available.

Have you seen the FAQs on Shrinking the log and database? You may want to read those. If you are running mass updates, you may want to review a FAQ that I created that discusses a method to limit log growth.

faq183-1534 - Shrinking Databases and Logs - SQL 7 and Higher

faq183-3141 - How to Use Batch Size to Speed Mass Updates, Inserts and Deletes



If you want to get the best answer for your question read faq183-874 and faq183-3179.
Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

 

[jcaulder]

After compiling some statistics, I can report that the main table in this database grows at a rate of:

200,000+ rows per day

with space growth of:
320MB per day

Maybe it's like you said, the transaction log can't grow fast enough to keep up with demand during these peak times. It appears to peak out at about 9:00am with about 153 transactions per minute.

I'm still experimenting with different combinations to see if I can get it to respond predictably and reliably as well as looking at other stats.