Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.
Course Of The Month
8 days, 21 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Stil not clear on sql server Transaction LOG backup
Posted on 2008-06-12
I'm not a DBA but we dont have a DBA here. I think i asked this question maybe 2 yrs ago but i'm still not clear on the procedure for backing up LOG transaction in production environment...
If I remember correctly, 2 jobs ago, the "DBA"...backed up the log every 15 mins and trucated it. So every 15 mins, it was backed up, trucated,.,,backed up, truncated.
Our log file is growing here... not yet very big...1 GB so far....But what is the correct policy/method of do this??
Here it says to truncate it:
http://support.microsoft.com/kb/873235
If I remember correctly, 2 jobs ago, the "DBA"...backed up the log every 15 mins and trucated it. So every 15 mins, it was backed up, trucated,.,,backed up, truncated.
Our log file is growing here... not yet very big...1 GB so far....But what is the correct policy/method of do this??
Here it says to truncate it:
http://support.microsoft.com/kb/873235
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
4
3
7 Comments
Truncating it is NOT necessary.
When you do a log backup, youre removing the inactive poritions of the log....of if you're in full recovery mode, you should be doing log backups....every 15-20 min is a good place to start...but it really depends on the requirements. You really won't need to shrink the log, so doing your trans log backups every so often will keep your log from growing out of control. Does that make sense?
BTW..truncating the log makes is so that you can't restore to a point in time...which you'll want to be able to do since you're in full recovery mode already.
When you do a log backup, youre removing the inactive poritions of the log....of if you're in full recovery mode, you should be doing log backups....every 15-20 min is a good place to start...but it really depends on the requirements. You really won't need to shrink the log, so doing your trans log backups every so often will keep your log from growing out of control. Does that make sense?
BTW..truncating the log makes is so that you can't restore to a point in time...which you'll want to be able to do since you're in full recovery mode already.
Active 4 days ago
I think I've got it finally..only took maybe 2 yrs :)
I've read that Db should be in full mode in production...this is to be able to retrieve lost data..correct?
If I actually want to shrink the size of the log, i can use shrinkfile ..correct? ( i want to do this in our test environment)
I've read that Db should be in full mode in production...this is to be able to retrieve lost data..correct?
If I actually want to shrink the size of the log, i can use shrinkfile ..correct? ( i want to do this in our test environment)
If you think you really need to shrink it,then use DBCC SHRINKFILE....do make sure you test it first.
If will help you recover to a point in time...so, if you want to recover to a point in time, you restore your last full backup, last differential backup (if you have one), and any trans logs in order up to the time you want to restore....
If will help you recover to a point in time...so, if you want to recover to a point in time, you restore your last full backup, last differential backup (if you have one), and any trans logs in order up to the time you want to restore....
Active 4 days ago
i think i dont understand the second paragraph...
if I backup the log every 15 mins..suppose i start at 1 PM.
At 1:00, i have a backup
At 1:15, i have a log backup
At 1:30 , i have a log backup
At 1:45...something happens and i want to recover. I should restore the backup from 1:30..correct?
-- so backup the transaction log but what is that last differential backup??
if I backup the log every 15 mins..suppose i start at 1 PM.
At 1:00, i have a backup
At 1:15, i have a log backup
At 1:30 , i have a log backup
At 1:45...something happens and i want to recover. I should restore the backup from 1:30..correct?
-- so backup the transaction log but what is that last differential backup??
if you can perform a tail log backup (final tlog backup...the 1:45 backup) then do so...otherwise, you would do this:
restore your full backup from 1
restore your tlog backups from 1:15 and 1:30. If the 1:30 is the last tlog bkup you have, then you're done. If you have the tlog backup from 1:45, you can restore up to the point in time of the error....say it happend at 1:40, you can restore up to 1:39 or so.
restore your full backup from 1
restore your tlog backups from 1:15 and 1:30. If the 1:30 is the last tlog bkup you have, then you're done. If you have the tlog backup from 1:45, you can restore up to the point in time of the error....say it happend at 1:40, you can restore up to 1:39 or so.
Active 4 days ago
One last question....you say it not needed to shrink the tlog
that link above has:
"To recover from a situation where the transaction logs grow to an unacceptable limit, you must reduce the size of the transaction logs. To do this, you must truncate the inactive transactions in your transaction log and shrink the transaction log file."
What if I shrink , but still have the tlogs...can I still recover the DB by using the tlogs? I would think the answer is yes...
that link above has:
"To recover from a situation where the transaction logs grow to an unacceptable limit, you must reduce the size of the transaction logs. To do this, you must truncate the inactive transactions in your transaction log and shrink the transaction log file."
What if I shrink , but still have the tlogs...can I still recover the DB by using the tlogs? I would think the answer is yes...
Featured Post
![[Webinar] Learn How Hackers Steal Your Credentials](https://filedb.experts-exchange.com/files/public/2017/5/21/e0b7778f-e79e-4b5d-9ec6-5496e40be42e.png)
Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
I've encountered valid database schemas that do not have a primary key. For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis. However, occasionally due to user error or a scheduled task…
Introduction:
When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database.
Problems start when you try to combine that with some "user input" pass…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 21 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
615 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.