Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Truncation of transaction log


Truncation of transaction log

Author
Message
Ryan007
Ryan007
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1672 Visits: 1595
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//
ajitkumarng-615318
ajitkumarng-615318
Mr or Mrs. 500
Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)Mr or Mrs. 500 (532 reputation)

Group: General Forum Members
Points: 532 Visits: 43
Try shrinking database
GilaMonster
GilaMonster
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53813 Visits: 44620
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


GilaMonster
GilaMonster
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53813 Visits: 44620
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

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sybrand
sybrand
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 9
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

brandzero5@gmail.com
GilaMonster
GilaMonster
SSC Guru
SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)SSC Guru (53K reputation)

Group: General Forum Members
Points: 53813 Visits: 44620
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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


sinhuat.aw
sinhuat.aw
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 5
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search