Transaction Log and mirroring - looking for dumbest explanation possible

Question

First of all I must admit that I struggle with concept of Transaction Log. I mean - I do understand that it is Log of all Transactions that happen on database, but when it comes to properly putting it into context within some task I obviously lack something. So, to anyone who will be answering the question - please feel free to expand on theory behind Transaction Log.

Main question is - I have SQL Server 2008 and 2 GB database that I need mirrored (has 12GB transaction log). If I wasn't mirroring that database I presume I could either switch to Simple mode or truncate the log after backup. But in this case - what should I do if I wish to keep that transaction log under control? As I understand - I need to keep that whole transaction log if I wish to be able to easily mirror database (just do Full backup).

Is there any way around this? Ideally I wish it was possible to do backup that keeps both MDF and LDF in 1 file every time and after backup is done Transaction Log (LDF) on database is reduced to 0. The problem with this scenario are incremental backups - if my first backup truncated log, I presume that second backup would need to reference first one if I want to do mirroring later (i.e. I would be stuck with keeping bunch of files instead of just one).

So - can anyone enlighten me on this subject? I understand that I am trying to fill in lots of holes here and that my proposed "solutions" may not be the best ones, but I would sincerely appreciate if somebody can push me in right direction on Transaction Logs, how they impact mirroring and best practices with those two.

Answer 1

The transaction log is a important method to restore your database in a specific time. If you have a large database > 500 GB and if you must restore your database from a full backup, this will cost very much time. Also if you full backup your database everytime, think about how long this backup could take.

A very easy concept for SQL Server can be: Set Recover Model of your Database Full

Create a Maintenance Plan(1) in SQL Server:

• Do FullBackup every Week maybe in D:\yourbackup\FullDBBackup.bak
• Do Differential Backup every two days in D:\yourbackup\DiffBackup.bak
• Do every 2 houers Backup your Transaction log in D:\Yourbackup\Tranlogbackup.trn

Create a Maintenance Plan(2) in SQL Server:

• Delete all older Files 8 days from D:\yourBackup*.bak
• Delete all older Files 3 Days from D:\yourBackup*.trn

In this case, you are able to recover your Database in a specifc time, very fast very easy. SQL Server will automaticly manage your "Backup" files, older files will be deleted after your specific time range.

I would suggest that you read about SQL Server Transaction Log here:

http://www.sqlservercentral.com/articles/Design+and+Theory/63350/

For using the Maintenance Plans in SQL Server just ask BING / google :D

you should build a small test db and test this before you go in production

Answer 2

In order to take advantage of mirroring you have to have the database in FULL recovery mode and will need to take transaction log backups in order to keep the log file from growing. If you don't need the log backups then just purge them after x amount of hours with a maintenance plan, but they need to be taken.

To clean up the environment you should remove the mirroring, switch the recovery mode to simple, get the log file size down via the recommended Paul Randal Way, switch back to full recovery mode, set up full and log backups, then re-initialize your mirror. You can try to get the log size down while the mirroring is in place but it will be way easier to remove it first. 1 GB shouldn't be too bad of a db to re-initialize.