3

We are using SQL Server Mirroring with a witness (High Safety mode) and we see the transaction log on the Principal Server has grown around 20 GB in few weeks. I looked around on SO and google for specific articles related to "Safe delete Transaction Log" in SQL Server but found none for my case.

My Questions:

  1. Would it be safe taking full backup of Principal Server and then delete the transaction log completely?

  2. Does it have to do something with Full Recovery/ Simple Recovery Mode?

  3. What would happen when the transaction log on the Principal Server is deleted and the after sometime Mirror Server will take the place of the Principal?

Note:

We are using SQL Server 2014 Enterprise Edition for Mirroring and Express version of it as a Witness.

Thanks in Advance!

ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
Sunny Sharma
  • 145
  • 5

1 Answers1

3

Transaction log is the most important element in SQL Server. It is like a sequential journal that logs all the changes made to the database and contains enough information to undo / redo the changes in the event of crash recovery to keep the database in a consistent state.

Highly recommend to read : Understanding Logging and Recovery in SQL Server

All your question are just because you dont have proper understanding of how transaction log works.

To keep transaction log in shape, you should take regular log backups (more frequently) on the principal server. Check - How do we handle transaction log maintenance for a mirrored database?

To answer your questions :

Would it be safe taking full backup of Principal Server and then delete the transaction log completely?

No this is not possible and not advisable to delete T-Log.

Does it have to do something with Full Recovery/ Simple Recovery Mode?

Mirroring uses T-log to replay everything from principal server to mirror server. So Full recovery is only possible. check : recovery models.

What would happen when the transaction log on the Principal Server is deleted and the after sometime Mirror Server will take the place of the Principal?

Not feasible. Please read the links that I have suggested.

Kin Shah
  • 62,545
  • 6
  • 124
  • 245