October 4, 2012 at 3:58 pm
Hello,
I am not a DBA but I am doing some DBA jobs. We have a database with full recovery mode, so the transaction log is keeping growing and growing.
I have read some articles, saying we can backup transaction log file to limit tansaction log file growing too big, and we never shrink or truncate the file, ect. Please advise details regarding transaction log file.
Thanks
October 4, 2012 at 4:19 pm
Perform a transaction log backup. While this will not shrink the log file itself (though that is sometimes possible to do, depending on the circumstances), it will allow SQL Server to re-use the space allocated, hence ceasing the growth of the file.
Sample below (assuming databasename of ABC123:
backup log [ABC123] to disk=N'C:\Database Backups\ABC123.bak' WITH NoFormat, NoInit, Name=N'ABC123 Transaction Log Backup'
Please note that you will need to ensure that the volume and directory for creating the backups (C:\Database Backups\, in this case) is created and has space.
October 4, 2012 at 6:26 pm
thank you.
I will practice it on a test database first. Do I have to schedule a job to do the transaction log backup many times or just one time, if many times, how often? After backup, do I have to do other things? I appreciate your time.
October 4, 2012 at 6:31 pm
You will need to continuously backup the transaction log, it's part of maintaining a database in anything except simple recovery. Most places I know do either 1 hour or 15 minute backups. You'll want to schedule a job to do this for you and have some kind of archive process put in place.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 5, 2012 at 1:32 am
Please read through these - Managing Transaction Logs[/url]
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
October 5, 2012 at 11:02 am
How often you take transaction log backup?
October 5, 2012 at 2:41 pm
deleted - misread context of question.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply