Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Stil not clear on sql server Transaction LOG backup

Posted on 2008-06-12
7
Medium Priority
?
186 Views
Last Modified: 2010-03-19
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
0
Comment
Question by:Camillia
  • 4
  • 3
7 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 2000 total points
ID: 21770386
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.
0
 
LVL 7

Author Comment

by:Camillia
ID: 21770456
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)
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21770471
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....
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:Camillia
ID: 21770558
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??
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21770593
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.
0
 
LVL 7

Author Comment

by:Camillia
ID: 21770642
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...
0
 
LVL 7

Author Comment

by:Camillia
ID: 21770773
i do get it. Amazing. Thanks.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question