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.
| 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).- LumbagoMy blog-> http://thefirstsql.com/2011/07/08/how-to-find-gaps-in-identity-columns-at-the-speed-of-light/ |
 |
|
|
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). |
|
|
|
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 ShawSQL Server MVP |
|
|
|
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! |
|
|
|
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 ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Mirroring - Transaction log size