February 4, 2012 at 8:36 am
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
February 4, 2012 at 8:51 am
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" 😉
February 4, 2012 at 8:58 am
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.
February 4, 2012 at 9:38 am
Please read through this - Managing Transaction Logs[/url] and http://qa.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
February 5, 2012 at 5:55 am
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply