5

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?

Tensigh
  • 225
  • 2
  • 8

1 Answers1

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.

Community
  • 1
Shanky
  • 19,148
  • 4
  • 37
  • 58