Transaction Log Backup
-
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.
-
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[/url]
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 -
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.
-
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; Theyll drag you down to their level and beat you with experience
-
Wonderfully worded advice opc.three. ^.^
.
-
Thanks for the info. It will be backed up to an external drive.
-
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.
-
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 -
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.
-
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 -
Looks like we're cooking with gas now. I used the Maintenance Plan to schedule Log backups against the test DB every hour and looks like the backup files are being generated nicely although very small files. What do you think? Ready to move to production.
-
That looks familiar 😉
If you're happy I say send it to prod!
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Dont forget to go through a couple restore/recovery tests 🙂
-
Went live with the Trans log backup this morning. As you can see the first file is huge. I guess that because it's never been done before. Hopefully this will stop the log file from growing out of control. Now what I would like to do is reclaim some of that disk space (after a few more full DB backups).
-
That makes perfect sense. You can now use DBCC SHRINKFILE (once) to reclaim log space. The command can be blocked from shrinking the file for various reasons so post back if you have trouble.
Pick a sensible size for the log to shrink it to and adjust your autogrow settings too. Speaking of that, what are the autogrow settings? If it was a small increment, like 10MB, you may have tens of thousands of Virtual Log Files (VLFs). To get rid of those you may actually want to shrink your log to practically nothing and then regrow it manually to that sensible size to get rid of VLFs.
How many rows are returned when running this:
DBCC LOGINFOThere are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 1 through 15 (of 33 total)
You must be logged in to reply to this topic. Login to reply