May 6, 2009 at 6:26 am
Hi all,
I have one database whose recovery model is full.
Now my transaction log is increasing which is more than mdf file. What is the correct way to truncate the log through maintenance plan? I do not want to change the recovery model.
I have gone through the same topic here and it says take your transaction log backup periodically to truncate the transaction log. But taking back up of transaction log is not reducing the size of the database log file(idf). Please suggest.
Ryan
//All our dreams can come true, if we have the courage to pursue them//
May 6, 2009 at 6:34 am
Try shrinking database
May 6, 2009 at 6:38 am
ajitkumarng (5/6/2009)
Try shrinking database
Bad idea. Lots of side effects and, besides, if the log is full it won't do anything other than fragment indexes.
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
May 6, 2009 at 6:42 am
sumanta.roy52 (5/6/2009)
I have gone through the same topic here and it says take your transaction log backup periodically to truncate the transaction log. But taking back up of transaction log is not reducing the size of the database log file(idf). Please suggest.
In full recovery you have to back the log up on a regular basis. If you don 't, the log file will grow until it fills your hard drive. Please read through this - Managing Transaction Logs[/url]
Backing the log up will not change the size of the log file. It only makes the space inside available for reuse. If you need to reduce the size of the log file, do a once-off shrink file. Do not shrink it down to nothing. You need to work out what size the log needs to be based on the frequency of your log backups and the activity on your database. Once you know that, set the log to that size (plus maybe 10% leeway).
Do not regularly shrink the log. Recommendations are to set it's size and leave it alone. Just make sure you're doing regular log backups.
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
June 6, 2014 at 6:23 am
Hi,
I know this question was asked a long time ago but I thought I would answer for anyone having the same problem as it was something I had a problem with as well.
You have to do a Full backup and then Transaction log backups depending on the how quickly your log is growing, I have mine set to every 10 minutes to keep it low. The following you need to do because SQL will not give you the space back any other way, what SQL is doing it takes the data out but it leaves the shell there with the same size, when the log backup is done SQL writes the data to Database and it is important to know that no rollback would be possible after that.
This is the Important part and the way to shrink the Transaction log is to set the Recovery to Simple in properties, then go to Shrink File and select log file (not data) this can be quick if you have done the full backup but it can also take some time depending on a couple of things that I am not going into now, but leave it and let it finish. When this is done go back to properties and set Recovery back to full. Now just keep on doing the backups as I stated in 2nd paragraph and you would be fine and if need be you can shrink it again, it is preferable to get your log backups right so that you do not have to shrink manually but if you need to do it you can.
Regards,
Brand
June 6, 2014 at 7:47 am
sybrand (6/6/2014)
This is the Important part and the way to shrink the Transaction log is to set the Recovery to Simple in properties, then go to Shrink File and select log file (not data) this can be quick if you have done the full backup but it can also take some time depending on a couple of things that I am not going into now, but leave it and let it finish. When this is done go back to properties and set Recovery back to full.
No need. You can shrink the log once you've run a log backup. Breaking the log chain is not necessary. You also didn't mention the requirement to run a full/diff backup after switching back to full recovery.
Also
when the log backup is done SQL writes the data to Database and it is important to know that no rollback would be possible after that.
That's not true.
All a log backup does is write the portion of the log to a backup file and then mark that portion of the log as reusable. Log backups don't write to the data file. Rollbacks aren't possible after a transaction commits, nothing to do with log backups.
http://www.sqlservercentral.com/articles/Administration/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
June 24, 2014 at 10:14 pm
I have similar issues, but I am doing transaction log backup every 12hours, and a full DB backup every 24hours.
can you help me in this thread - http://www.sqlservercentral.com/Forums/Topic1585732-357-1.aspx
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply