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!

  • 1
    No, you cannot and should not delete the transaction log. There is a reason it grew, and you can be proactive about this instead of panicking and trying to delete things you need. See dba.stackexchange.com/q/29829/1186 – Aaron Bertrand Jul 17 '15 at 16:17
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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