SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Transaction Log Backup


Transaction Log Backup

Author
Message
midnight251
midnight251
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 21
Our company has never backed up the transaction log file, and is now about 143 gig, with about 35 gig left on the drive, and I have to get this under control. As you might have guessed, I'm relatively new to SQL. We do a full db backup every night. I would like to start a log file backup about every two hours during business hours to truncate the log file and limit the growth, and after a bit, re-size the log file. I will be doing this through Management Studio. I will be backing this up onto another drive into a backup folder. My question is regarding the overwrite media options. Do I keep appending to the existing backup set? Do we create new files for every time the log file gets backed up, and if so, how do we do this. I guess I'm asking for the proper way to set this up, and make a restore (if needed) as easy as possible. We are on SQL 2008R2.

Any help or direction would be greatly appreciated to get this on track.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37218 Visits: 14411
midnight251 (2/8/2013)
Our company has never backed up the transaction log file, and is now about 143 gig, with about 35 gig left on the drive, and I have to get this under control. As you might have guessed, I'm relatively new to SQL. We do a full db backup every night. I would like to start a log file backup about every two hours during business hours to truncate the log file and limit the growth, and after a bit, re-size the log file.

You may want to take an initial backup and then shrink it one time to a reasonable size to reclaim some of that 143 GB but do not shrink the log after every backup. Please read this article:

Managing Transaction Logs By Gail Shaw

I will be doing this through Management Studio. I will be backing this up onto another drive into a backup folder. My question is regarding the overwrite media options. Do I keep appending to the existing backup set? Do we create new files for every time the log file gets backed up, and if so, how do we do this. I guess I'm asking for the proper way to set this up, and make a restore (if needed) as easy as possible. We are on SQL 2008R2.

Any help or direction would be greatly appreciated to get this on track.

I like to take backups where there is only one backup per file and the file is named with a date and timestamp, i.e. I never append a backup to an existing file. This keeps my restore scenarios simple because I never have to wonder what each file contains and I can tell from the name when it was taken.

You should look into automating your backups. The Maintenance Plan Wizard is a quick way to setup automated tran log backups and can give you the named files one backup per file that I am talking about.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
midnight251
midnight251
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 21
Thanks for the reply, that's great info. I'll be on it first thing tomorrow. I take it that I can do this while the DB is in use? We are running MS Dynamics NAV ERP using the DB. I'm going to get this going tomorrow.

Thanks again for the response.
MyDoggieJessie
MyDoggieJessie
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11768 Visits: 7443
midnight251 (2/10/2013)
Thanks for the reply, that's great info. I'll be on it first thing tomorrow. I take it that I can do this while the DB is in use? We are running MS Dynamics NAV ERP using the DB. I'm going to get this going tomorrow.

Thanks again for the response.
There won't be any issue taking either Full, Differential, or Transaction Log backups while your server is in use. There will be some contention (disk IO) as a result of the backup process but ordinarily, with a decent disk subsystem this won't be an issue. Are you going to be backing this data up locally or to a remote storage device (preferred)?

______________________________________________________________________________
"Never argue with an idiot; They'll drag you down to their level and beat you with experience" ;-)
Bill (DBAOnTheGo)
Bill (DBAOnTheGo)
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1219 Visits: 599
Wonderfully worded advice opc.three. ^.^

.
midnight251
midnight251
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 21
Thanks for the info. It will be backed up to an external drive.
midnight251
midnight251
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 21
Ok, just created a Maintenance Plan to backup the Trans Log file. It's going to run Mon-Fri from 7:00am to 7:00pm every three hours. I did say for it to create a new file for every database, I hope this will create me a file for every trans log backup. I also didn't see an option to truncate the log file in the Maintenance Wizard. It's going to kick off tomorrow morning aftar a full DB backup. Got my fingers crossed.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37218 Visits: 14411
midnight251 (2/12/2013)
Ok, just created a Maintenance Plan to backup the Trans Log file. It's going to run Mon-Fri from 7:00am to 7:00pm every three hours. I did say for it to create a new file for every database, I hope this will create me a file for every trans log backup. I also didn't see an option to truncate the log file in the Maintenance Wizard. It's going to kick off tomorrow morning aftar a full DB backup. Got my fingers crossed.

The log backup will mark portions of the log that were backed up as reusable. There is nothing to explicitly 'truncate.'

Is 3 hours often enough? Your log backup interval should represent the amount of data you're organization is willing to lose should the server where the database is hosted be completely lost.

I would run the Maintenance Plan in a test environment and see how it goes. If it does not output file names in the folder structure you like then change the Maintenance Plan until it does, then configure that in your production environment.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
midnight251
midnight251
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 21
Sound advice opc.three, just removed from the prod DB, and I'll configure on our test DB tomorrow, though not a lot of transactions going through, but at least I'll see how the files are being created, even if no transactions. Over time this would create hundreds/thousands of files, but I guess you could purge them based on the last time of a full backup? I'll keep you posted. Thanks for all the help.
Orlando Colamatteo
Orlando Colamatteo
SSC-Dedicated
SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)SSC-Dedicated (37K reputation)

Group: General Forum Members
Points: 37218 Visits: 14411
No worries if there is little or no database activity, a log backup will still be produced.

Use the Maintenance Cleanup Task to delete old backups.

__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
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