5

This question already has an answer here:

We just upgraded our Windows Server 2003\SQL Server 2005 setup to Windows Server 2012/SQL Server 2012. Both configurations have a mirror with NO witness server.

On the old setup the transaction logs remained small, about 22 GB total. Now they're over 160 GB and the partition of the new server is getting full. I need to reduce the transaction log file sizes.

However, all of the descriptions I've read say to switch the recovery mode from Full to Simple then back to Full. This won't work because we have a mirror so the recovery model MUST remain in Full recovery mode. I couldn't change recovery modes unless I break the mirror.

What would be the best course of action to reduce the log file sizes? Should I go ahead and break the mirror, switch from Full -> Simple then back? Does anyone have experience with this kind of setup?

marked as duplicate by Max Vernon, RolandoMySQLDBA, Colin 't Hart, Paul White sql-server Jun 25 '15 at 23:38

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 1
    Do you run regular log backups? – James Anderson Jun 25 '15 at 6:33
  • I'm new to this firm. I'll have to check if there is an automated process for this. On the old server, the transaction logs were small so there must have been. – Tensigh Jun 25 '15 at 7:29
  • This isn't really a duplicate - I haven't found a question that addresses a scenario with a mirror with no witness. I'm sure that will fall on deaf ears to those who have marked this, but it's not a duplicate. – Tensigh Jun 26 '15 at 0:55
  • Your situation is a bit diffeeent but your core problem and the solution are the same – James Anderson Jun 26 '15 at 5:41
  • I didn't know that the solutions would be the same, that's why I asked. People on SE can be brutal about that sometimes so I take great care to not ask duplicates. Sadly, it doesn't always matter. – Tensigh Jun 26 '15 at 12:55
2

Direct answer to your question

What would be the best course of action to reduce the log file sizes?

In full recovery model only transaction log backup, perhaps frequent transaction log backup in your case, would truncate the logs and keep check on log file size. There is no other way to truncate logs. I strongly suggest you to read Why transaction log keeps growing and run out of space

Should I go ahead and break the mirror, switch from Full -> Simple then back? Does anyone have experience with this kind of setup?

No please dont do that this will break the mirroring and you would have to re-establish it and it would be big hassle for you if you have got big database and mirror is located far from principal. Anyways if you do that and somehow configure mirroring after few days you would again face this issue because you actually did not solved it.

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