huge transaction log file

Question

huge transaction log file

Sarsoura

Ten Centuries

Points: 1220
More actions
February 4, 2012 at 8:36 am

#252311

I am using full backup on daily basis.
The transaction log file is continually growing.
I read that if i backed up the transaction log say every 15 minutes this will resolve the issue.
1)Currently on backing up the transaction log frequently the size does not shrink
2) if I used full backup once a day and backing up the transaction log every 15 minutes
on restoring will it be enough to restore the full backed up database or how I will restore it so as not to loose anything
What is the best way to do that and to make the current transaction log shrink

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

sohairzaki (2/4/2012)
I am using full backup on daily basis.
The transaction log file is continually growing.
I read that if i backed up the transaction log say every 15 minutes this will resolve the issue.

not necessarily. If the amount of transactions between log backups is high then the log will grow, providing you have enabled autogrow

sohairzaki (2/4/2012)
1)Currently on backing up the transaction log frequently the size does not shrink

How often are you taking log backups? Read my statement above regarding the amount of transactions. What is the initial size of the transaction log?

sohairzaki (2/4/2012)
2) if I used full backup once a day and backing up the transaction log every 15 minutes on restoring will it be enough to restore the full backed up database or how I will restore it so as not to loose anything

using a full backup and t-log backups every 15 mins you will lose less work (up to 15 mins at most)

sohairzaki (2/4/2012)
What is the best way to do that and to make the current transaction log shrink

That is a manual operation, a transaction log backup does not shrink the log file, it does truncate it and mark internal space reusable. If the log has grown if most likely needs the space, what is the current size of the log?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 2

First question you need to ask yourself is which recovery model suits my needs? Do you need a point in time restore or will a daily backup suffice?

If a daily backup will see you through, switch the recovery model to simple mode.

If it wont take yourself over to these articles and study them.

http://www.sqlservercentral.com/stairway/73776/[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Please read through this - Managing Transaction Logs[/url] and http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 4

Here's another short article[/url] to get you started on log backups.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning