Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Truncation of transaction log Expand / Collapse
Author
Message
Posted Wednesday, May 6, 2009 6:26 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 1,602, Visits: 1,572
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//
Post #710991
Posted Wednesday, May 6, 2009 6:34 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 15, 2013 2:34 AM
Points: 530, Visits: 27
Try shrinking database
Post #710997
Posted Wednesday, May 6, 2009 6:38 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
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 2008, MVP
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

Post #710999
Posted Wednesday, May 6, 2009 6:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
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 2008, MVP
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

Post #711002
Posted Friday, June 6, 2014 6:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, July 24, 2014 3:49 AM
Points: 1, Visits: 8
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
Post #1578263
Posted Friday, June 6, 2014 7:47 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:58 PM
Points: 42,466, Visits: 35,532
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 2008, MVP
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

Post #1578308
Posted Tuesday, June 24, 2014 10:14 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 2:46 AM
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

Post #1585733
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse