January 20, 2007 at 7:48 am
I have a customer with RightFax which uses a SQL Server 2000 database. The problem is the log file - back in November whilst looking to free-up disk space on the server I noted that the data file was 11 Megs but the log file was 5 Gigs. I ran a log backup with truncate_only and then shrunk the log down to 1 Meg. Fine.
Now nearing the end of January, the log has grown to 200 Megs, and the vendor contact claims "that behavior is by design" but frankly I don't buy it. BTW there is a nightly full backup of the db.
I'm not a log expert, but in November when I used the undocumented command
DBCC LOGINFO('RightFax')
I can see 371 pages in the virtual log, but only four (4) of those show a completed status. Is it possible that transactions are not being committed in RightFax?
PS - the RightFax does work.
January 20, 2007 at 1:49 pm
As long as the database is in Full Recovery mode, the transaction log will continue to grow until you run a transaction log backup.
You can setup scheduled transaction log backups using a maintenance plan. I usually set them up to run every 15 minutes, 24x7, and to retain the log files for at least 3 days.
Of course, you will also need to have full backups scheduled, but you said you already have. If you don't, setup a daily full backup with a maintenance plan, and save it for 3 days.
January 21, 2007 at 8:10 am
I found two good articles:
A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
http://support.microsoft.com/?id=317375
INF: Causes of SQL Transaction Log Filling Up
http://support.microsoft.com/kb/110139/
...from what I understand, SQL Server is generally supposed to re-use space in the tran log after trans are committed. There is an automatic checkpoint mechanism that helps accomplish that. The articles mention several causes for above-normal log growth, including beginning a tran without a matching commit or rollback, due to either poor design or a lost network connection.
They also mention "typical" log size to be 15-30% of the data file size; in the case of this RightFax install, it is 5000% so there is definitely something wrong, don't you think?
January 21, 2007 at 11:34 am
The second link only applies to SQL Server 6.5.
I am fairly certain that the problem is what I said it was in my previous post. When the database is in Full Recovery mode, transactions are saved in the transaction log file until you execute a BACKUP LOG command. The log file will continue to grow until you do a log backup.
The reason for this is that the transcations logs are what allow you to do a point-in-time recovery of the database by restoring from a full backup and then restoring from the individual transaction log backups in order to bring the database up to a point in time.
You should read the following topic from SQL Server 2000 Books Online for a more complete understanding of backups, restores, and recovery models:
http://msdn2.microsoft.com/en-us/library/aa196685(SQL.80).aspx
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply