Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 High Availability (2008)
 Mirroring - Transaction log size

Author  Topic 

Leen
Starting Member

2 Posts

Posted - 2011-10-18 : 10:51:09
Hello,

I'm a programmer and we are using a SQL server 2008 database (Standard Edition).
I have a database that is being mirrored to another location.
The whole mirroring think works perfectly, but we have one problem: the transaction logs keep on growing and growing.
We can solve this problem by stopping the mirroring relation, backing up the logfiles, shrink the logs files and applying the log files again.
But this needs to be done manually and every 2 weeks (on several servers).

Is there any way to control this or solve this problem? How can we avoid to have to remember this every 2 weeks and do it manually?

Thanks for the help!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-10-19 : 06:50:13
Mirroring doesn't remove the need for doing backups! You must always back up your databases, and with a full recovery model you must back up the log files on a regular basis (I'd recommend every 15 mins for a production system).

- Lumbago
My blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/
Go to Top of Page

Cindyaz
Yak Posting Veteran

73 Posts

Posted - 2011-10-19 : 10:22:12
1. Database has to be in Full recovery mode if it's participating in mirroring session.
2. If you database is in full recovery mode, your log file will continue to grow unless you back it up (backing up log marks the space within the log file for reuse).You'll need to schedule transaction log backup (as mentioned by Lumbago).

Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-19 : 10:22:57
The DB is in full recovery, hence you need to do regular log backups.
Please read through this - [url]http://www.sqlservercentral.com/articles/64582/[/url]

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Leen
Starting Member

2 Posts

Posted - 2011-10-20 : 03:07:51
Hey all,

thanks for the advice.
Now I see where the problem is. We do backup our database, but not the transaction logs (I'm a programmer, no DBA!).
I will change our backup scripts to also backup de transaction logs, and I suppose it will be a lot better!

Thanks!
Go to Top of Page

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-10-20 : 09:32:46
Once a day is not enough (if that's how often the DB is backed up) that. Since you have database mirroring, I assume the DB is important. Please read the article I referenced.

--
Gail Shaw
SQL Server MVP
Go to Top of Page
   

- Advertisement -