Can someone try explain to me why the log file gets filled up so quickly? Our training database is 12gb, but gets little user usage. The transaction log file is around 20mb at the moment. For some reason every now and then the log file is up to 12gb and then backup tran log fails due to disk space errors. I have to manually truncate and log and shrink it. What are the potential problems and how can I solve this? Thanks.
I suppose you have Recovery Model Full. Do you need that in training database?. If not change recovery model to simple. Luis Martin Moderator SQL-Server-Performance.com All in Love is Fair Stevie Wonder All postings are provided “AS IS†with no warranties for accuracy.
you are correct - Recovery model is on Full. Can you briefly explain the difference between full, Simple and bulk-logged please? Our production server has the same issue recently though - tran log file is around 25mb but there are times when for some reason it grows to about 12gb. It seems that this happens around daily backup times. coincidence or is there any correlation?
You can select one of three recovery models for each database in Microsoft® SQL Server™ 2000 to determine how your data is backed up and what your exposure to data loss is. The following recovery models are available: Simple Recovery Simple Recovery allows the database to be recovered to the most recent backup. Full Recovery Full Recovery allows the database to be recovered to the point of failure. Bulk-Logged Recovery Bulk-Logged Recovery allows bulk-logged operations. The recovery model of a new database is inherited from the model database when the new database is created. For more information read: Using Recovery Models in Books on line. Luis Martin Moderator SQL-Server-Performance.com All in Love is Fair Stevie Wonder All postings are provided “AS IS†with no warranties for accuracy.
Thanks for the info. Our production database is obviously full mode. It has run into this problem og log file hitting 12gb. Any idea on that... when the normal log file size is 25mb before it is backed up? Thanks again.
Are you only performing one log backup a day (after the full backup)? If so, this is normal activity in your database and you need to be taking transaction log backups on a more frequent schedule in order to keep the active portion of the transaction log smaller.
quote:Originally posted by Haywood Are you only performing one log backup a day (after the full backup)? If so, this is normal activity in your database and you need to be taking transaction log backups on a more frequent schedule in order to keep the active portion of the transaction log smaller. Log Files are backed up hourly... Should the full db back up happen at a different time to the transaction log backup. Tran log backed up on the hour - full DB backup happens at 2am. Thanks again.
I suggest to backup transaction log more frequently, said each 15 minuts. Luis Martin Moderator SQL-Server-Performance.com All in Love is Fair Stevie Wonder All postings are provided “AS IS†with no warranties for accuracy.
Well, if you're backing up hourly then it's probably some maintenance operation causing the bloat..or very poor transaction management and you're not really truncating the active portion of the log hourly. Does the log grow all day long or mostly in one shot?
Hi, i would suggest to refer below KBs and threads ,for cause of Transaction Log becoming full and how to reduce Locks and how to stop it : Causes of SQL Transaction Log Filling Up http://support.microsoft.com/?id=110139 http://www.support.microsoft.com/?id=317375 How to stop the transaction log of a SQL Server database from growing unexpectedly http://support.microsoft.com/?kbid=873235 and threads: http://groups.google.com/group/micr...40ed8742075475d?sa=X&oi=groupsr&start=1&num=3 http://groups.google.com/group/comp...ac9ed81dd813cd9?sa=X&oi=groupsr&start=0&num=3 http://www.dbazine.com/sql/sql-articles/mullins-sqlserver Regards Hemantgiri S. Goswami ghemant@gmail.com "Humans don't have Caliber to PASS TIME , Time it self Pass or Fail Humans" - by Hemantgiri S. Goswami ------------------------ http://hemantgirisgoswami.blogspot.com
quote:Originally posted by Haywood Well, if you're backing up hourly then it's probably some maintenance operation causing the bloat..or very poor transaction management and you're not really truncating the active portion of the log hourly. Does the log grow all day long or mostly in one shot? One shot...
quote:Originally posted by somear quote:Originally posted by Haywood Well, if you're backing up hourly then it's probably some maintenance operation causing the bloat..or very poor transaction management and you're not really truncating the active portion of the log hourly. Does the log grow all day long or mostly in one shot? One shot... Then it's probably some maintenance operation. Check your job schedules and see if anything correlates with times...
Ok Thanks. Last question.. If I do hourly backup's on the hour of the transaction log, and do a full backup at 1.30a.m. 12:00 log 01:00 log 01:30 Full db 02:00 log 03:00 log If I had to restore the DB at 3am would I apply the full db and then the 2.00 and 3.00 logs? I suppose my question is, is the log file at 2.00am all trans from 1:00-2:00 or from 1.30 to 2:00?
Take a look here:http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=17311 The problem is caused by long running transaction and/or indexes rebuilding.
Yes if you need to restore database then you need the data from last database backup till last transaction log backup. The log information will be stored from 1.30 onwards until the execution of Tr.log backup job at 02.00. Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing.
