0

This question already has an answer here:

My system has the main database as a principal (reads/writes only to that database) and then a mirror database on a different site that receives all database updates from the principal database.

This is all working fine, but the transaction log on the principal database is close to 80GB and growing. I have tried shrinking the log but that task fails every time. In my opinion I would not need the log at all, since all updates are mirrored to the other site.

Principal database needs to be in full recovery mode for the mirroring to work.

Can you give me some steps on how to get rid of that annoying transaction log or how to get it into a manageable size? Since the mirror site is my recovery method, can I just delete in?

marked as duplicate by Jon Seigel, RolandoMySQLDBA, Paul White, Michael - sqlbot, Max Vernon Mar 10 '14 at 3:16

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.

migrated from stackoverflow.com Mar 7 '14 at 17:00

This question came from our site for professional and enthusiast programmers.

0

Your principal database is in full recovery mode which means you cannot (by rule) shrink your transaction log until you take a transaction log backup.

Here is some information from Microsoft on the subject:

http://technet.microsoft.com/en-us/library/ms191429.aspx

http://technet.microsoft.com/en-us/library/ms179478.aspx

In SSMS, highlight your database in the Object Explorer. Then right click it, choose Tasks then Back up... Next, choose your database. Change your backup type to Transaction Log. Back up to disk somewhere that has plenty of storage (at least as much as your transaction log).

At 80GB, your transaction log backup will take a long time. Once that's done you will be able to shrink your transaction log.

I would recommend running a transaction log backup regularly in order to keep your transaction log from growing out of control.

To answer your question, no, you cannot delete a transaction log (while the database is online) and if you do delete it, you risk losing any data that has not been flushed.

ALSO, Microsoft says you cannot take a transaction log backup until you run a full backup. If you have never run a full backup on the database in question, run that first before the transaction log backup.