February 16, 2011 at 7:23 am
I get an alert that one of my servers is out of disk space on its only drive. After finding out that the datafile was only 50mb and the tlog file was 55 GB!! I realized I needed to truncate the log.
After truncating the log I was able to reclaim almost all of the disk space back! That was fun!
I then decided I wanted to do something to prevent this from happening again so I turned to the maintenance plans.
After creating a TSQL plan for each user db to backup and dbcc shrink the logs I began to wonder how this would effect the Scheduled FULL backups.
How does truncating and shrinking the log effect the FULL backup? I remember somewhere reading about LSN when restoring. Am I ok at this point or do I need to create a new backup each time i run the log truncation process? (does truncating reset the log LSN?)
Thanks!
February 16, 2011 at 7:31 am
Please, please, please don't run scheduled shrinks. If you explicitly truncate the log (which you can't on SQL 2008) you will need to take another full backup before you can run further log backups. Explicit log truncation (backup log ... truncate only) is again something that you should not be doing.
Please read through this: http://www.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 7:33 am
The question is - are you running transaction log backups? If not, then the simplest thing to do would be to set the recovery model to SIMPLE. This will automatically truncate the log as it is checkpointed, and your full backups will be able to be restored as necessary. If you keep the recovery model as FULL, then you would want to schedule regular transaction log backups along with your full backups to capture the activity occurring between the full backups. The transaction log backup will then truncate the transaction log after it is backed up.
http://msdn.microsoft.com/en-us/library/ms175477.aspx will give you some explanations of the different backup types and how to use them.
Hope this helps.
February 16, 2011 at 9:39 am
Gila,
The Database is in Full Recovery mode.
When I first connected to the instance I saw two maintenance plans. A full database backup and a transaction log backup. So if it was taking transaction log backups why was the tlog growing and growing?
I'm reading the link you sent me now..
February 16, 2011 at 10:05 am
Good question. I can't answer that, you're the only one who can.
Was the maintenance plan scheduled, running and succeeding?
What was the log_reuse_wait_desc in sys.databases for that database/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 16, 2011 at 10:39 am
Ok please tell me if I understand this correctly...
In Full Recovery mode, you have take a Full backup to which transactions will be pilling up on a transaction log. You must backup these transactions to a .trn file often which will determine your point of recovery time. So lets say you have ONE TRN file being backed up hourly starting at 8am with 2mb of data per hour.
8am 2mb | 9am 4mb | 10am 6mb
This file will grow and grow and grow until...you backup your TRN file (or in other words backup your log backups) I think this is where I am getting lost?? Am i right?
February 16, 2011 at 10:53 am
I should probably state the problem I am having...
I have several databases where the transaction logs are several GB in size and getting ready to use all the space on the drive.
I now realize that truncating the log file is not the same as shrinking it. But how does shrinking effect the full backup and the LSN or does it?
February 16, 2011 at 11:22 am
These are two different things. When you back up a log file, it is automatically truncated, emptying out the space allocated with all of the work that was backed up. Shrinking the log file takes the space not being used in the file and gives it back to the operating system.
Example. You have a 2GB log file that has 1.95GB filled with transactions. When you back it up, you would then have a 2GB log file with 0.5GB of transactions. The other 1.45GB would be in the back up file.
If you have a 2GB log file with 1.95GB of transactions and you SHRINK it, it will then be 1.95GB. If you shrink it AFTER the back up, you would have a 0.5GB log file.
Does your maintenance plan do a transaction log back up? If NOT, then you CANNOT do a point in time recovery. If you do not need to do a point in time recovery, then set your Recovery Model to Simple.
And again, read this link for more info http://msdn.microsoft.com/en-us/library/ms175477.aspx
February 16, 2011 at 11:49 am
Yes, I thought upon truncating the transaction log file automatically shrank. I now know better. I did however go into the database properties and set the AUTOGROWTH MAX file size to be less than the size of the physical disk.
What happens just before, during, after the restricted file growth limit is reached?
Do you loose transactions?
Warning issued?
etc?
February 16, 2011 at 12:06 pm
If the log file reaches capacity, your database will stop functioning. Make sure that you set up an alert in SQL Agent to watch the % Log Used perfomance counter so that you are told that something is up BEFORE the log file fills up.
The one question that you have not answered, however, is if you need to be able to do a point-in-time recovery. If not, you can save yourself all sorts of problems by setting the recovery model to simple. That will eliminate the uncontrolled growth issue faster than anything else, as the log file will keep itself to a minimal amount of space.
February 16, 2011 at 1:23 pm
Sorry UDP you are right...
This is a highly transactional db and we do need to keep it in FULL recovery mode.
Thanks!
February 16, 2011 at 1:43 pm
Vertigo44 (2/16/2011)
This file will grow and grow and grow until...you backup your TRN file (or in other words backup your log backups) I think this is where I am getting lost?? Am i right?
Huh? The trn, the log backup is just a backup file. Nothing fancier. You would not more back up the log backup then you would make backups of the full database backup. (copy to tape/offsite storage, sure)
Don't append tran log backups to one file. Each to their own file, each timestamped with the date and time. Makes it far easier to restore. Maintenance plan does that by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply