8

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.

2 Answers 2

5

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

3
  • I do not have a problem with full backups & time since I can stop whole system until they finish. One question on your answer - do I truncate Transaction log in this scenario? If not, then I understand that I only need just latest FullDBBackup.bak to bring database back up? I do not care about specific points of time - all I care is latest version of DB that I get when I do backup. Meaning - I do not need transaction logs; I am just keeping them only because of mirroring. Is there a way around that?
    – nikib3ro
    Jun 21, 2012 at 11:59
  • 2
    1. you can'T truncate your log :D 2. If you take a backup of your tran log, SQL Server will give this space free for reuse in your logfile. just simple test dbcc sqlperf('logpsace') bevore the backup and then do the same after your backup. and in the end, you need transaction log...just test my example
    – user9399
    Jun 21, 2012 at 14:28
  • I finally got to implement your suggestions in test environment and so far I like what I'm seeing. Just please let me know if I understand this right - if I now need to restore database, I would first use FULL backup I've made, and then using combination of differential backups and transaction logs, I would be able to restore my database to certain point of time? Am I right? And also I presume that for mirroring, I would just need to stop the server and take full backup? Or will I need Transaction Logs to setup mirroring? Thanks again for all your help and answers!
    – nikib3ro
    Oct 24, 2012 at 18:24
3

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.