Transaction log best practice?

[Stanleyj]

I recently disovered due to an ugly situation that transaction logs are needed and i was wanting to see what others are doing in regards to truncating the logs.  I've been reading all over the place on how some say you shouldnt truncate until after a full backup and then others saying they truncate every time the log backup runs and i just am not sure what the best method is.

Because we were only backing up the DB once a day with no logs we lost what i have now learned the hardway was an unexceptable amount of data.  So logging is on and currently its doing about 200mb of transaction data every hour. 

My thoughts are to run the log backups every 2hrs during the work window (7am - 8pm) with truncation and then the normal nightly full which runs at 10pm. 

To not run into the "unexceptable" dataloss scenario again does this sound like solid plan?  

[Will_Restore]

It's a trade off in resources vs recovery window.  Can you handle 2 hours of data loss?  Can your system handle more frequent backups? 

[Marianne]

I have seen losts of customers doing hourly log backups. Even seen some using cmd to get NBU scheduler to kick off log backups more often, like every 20 or 30 minutes. As per Will's post - how much data loss can you afford? Backup and restore should not be your only protection if you cannot afford any data loss.

[Nicolai]

What type of database are we talking about - recovery scenario are different from database to database.

I would day log backup every 2 hours from 7AM to 8PM should like a reasonable rationale.

Archive logs are important of "point in time recovery" and are almost as important as database backup, because you can recover from older backup using archives files. 

[Stanleyj]

This is a sql database being used by our company's imaging system which management found out recently is probably the #1 used system in our entire organization.  We lost 24 hours of data because the database was only being backed up 1 once a day.  It was my fault for not doing something sooner because I have been trying to tell people for years we needed to take a closer look into this system but because we've never had an issue it was out of sight out of mind.  Well now of course it has all hit the fan and changes are being demanded.  

It was initially stated that we could not afford any data loss.  Meaning zero seconds, but were just not equipped for that type of response at the moment.  So we all agreed that 2 hrs is acceptable at the moment considering we were at 24.  But this is my first time doing any type of transaction log backup so i don't know the strain it puts on a system and or even how to restore. 

So since there asking for zero data loss and at the moment my only real solution is using netbackup is it possible to not truncate every 2 hours and just run normal log backups and then truncate after the full runs at night?  Or is that not really buying me anything extra? 

The situation we experience was the database got corrupt during a hardware swap (which we were working on getting corrected with Microsoft) and server 2012 prompted me to run a check disk on the SAN drive that housed the database.  Thinking that maybe the server saw an MPIO issue or something i let the server reboot and run.  Never in my wildest dreams did i think it was going to delete my database.  Just a complete malfunction in thinking on my part because check disk has never ever ever done anything in my experience but delete files it cant read.  Lesson learned the hard way.

I truly appreciate everyone's thoughts. 

[Will_Restore · Accepted solution]

From the NetBackup for SQL Admin Guide:

transaction log backup

A backup of the inactive portion of the transaction log. Typically, this
portion of the transaction log is truncated after it has been backed up
successfully.

(Note that a full backup does not truncate the transaction log.)

---------

Set up log backup with 2 hour schedule since that is current requirement.  When time permits, try 1 hour schedule (or less) and review the impact (it may be very little). 

[Nicolai]

You will never archive zero time data loss with any backup application. zero data loss require hot standby databases like Oracle DataGuard.

Please note transaction log backup may create many small image files on the destination storage - e.g tape. Meaning restore speed will reduced.

I would create a advanced disk/MSDP pool containing 1-2 week of archives files, and then by SLP move arhive files to tape.