Principle log file is Growing on Mirroring

Question

Hi all,

I have 2 years of experience on Sql server, now a days we have to handle L2 support.

I have faced one issue on last night, Principle log file is growing continuously how can i handle this type of situation and why the log file is growing? Please give me reply soon..we have to handle this issue today.

Answer 1

One reason probably due to rebuild indexes

BACKUP LOG TO DISK = 'Nul', then run a DBCC SHRINKFILE command. 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Scheduled log backups that is the way to mark unused free space to re-use so your log file does not grow up too much

---

http://uk.linkedin.com/in/ramjaddu

Answer 3

If you want to shkink the transaction log now, you have to break the mirror, switch the database recovery model to simple, shrink the log file, switch back the recovery model to full , take a full backup+transaction log backup and reestablish the mirror.

Then yoo have to monitor the transaction log file usage and size. As mentioned in a previous reply you must schedule to take transaction log backups frecuently in order to control the size

Regards

---

Javier Villegas | @javier_vill | http://sql-javier-villegas.blogspot.com/

Please click "Propose As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

Answer 4

Hi Ravi Chunduri,

Regarding to your description, as Uri mentioned you can try to use TSQL BACKUP LOG. But the log backup that truncating the log is not physically shrinking the log. It's good to understand the Truncating the Transaction Log.

Another thing is that regularly performing physical shrinks of your logs is not necessarily a good idea. You should manage the log size with your transaction log backups. You don't want the logs continually growing; shrinking. You want the logs to be at the appropriate size need for the database activities and managed with log backups. Please refer to the article about shrinking files.

---

Regards, Amber zhang

Answer 5

Javier

Why to break mirroring, backup log file and then DBCC SHRINKFILE...?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 6

Maybe it would be a good start to find out why the logfile is growing and what is preventing the reuse... There are multiple reasons for this:

1) Open transactions

2) Mirroring out of sync

3) Replication

4) Missing log backups

The easiest way to find that out is to do a SELECT log_reuse_wait_desc from sys.databases WHERE name=<YourDBName>

Answer 7

Hi Uri Dim ant,

Thanks for you help. Can we take the transaction log backup on principle server? I guess, if we take the transaction log backup may be it is impact to mirror server. Can you give me brief explanation?

---

Raveendra

Answer 8

Hello Raveendra,

As you might already aware of that, Database Mirroring requires the database to be in Full Recovery Model. When a database is in Full recovery model, all the transactions are logged in the T-Log file, which will in result grow the t-log file. When you initiate a Transaction log backup, the inactive portion of transaction log file will be cleared and can be reused for future transactions. Thus, you can control the t-log file growth; however, it can grow because of a long running transaction.

As Uri said earlier, you should take transaction log backup, and make sure you take it in frequent interval. To answer your question, you can take the transaction log backup only on the Principal server, if you are using SQL Server 2005/2008/2008 R2.

Hope, this may answer your query :)

---

SKG: Please Marked as Answered, if it resolves your issue.

Answer 9

Thanks Ganguly for your necessary information.

Can we truncate the transaction log backup of principal server? if we truncate, what happen exactly on the background and is it impact to mirror server?

---

Raveendra

Answer 10

Hello Raveendra,

Backup Log with Tuuncate only will not work as the command is no more supported on SQL Server 2008 onwards. Is there any specific reason you want to truncate the log? As I said earlier, frequent log backup will clean up the inactive portion of the log, so your log file will not grow.

---

SKG: Please Marked as Answered, if it resolves your issue. (b: http://sudeeptaganguly.wordpress.com)

Answer 11

Thanks Ganguly,

I have one doubt, in case if we truncate the transaction log file of principle server on 2005 mirroring, what happens exactly background and is it impact to mirror server?

---

Raveendra

Answer 12

Raveendra,

Why you want to truncate the log file? Every transaction on the Principal database will commit first on the Mirror database (unless you are running Mirrroring in Asynchronous mode). Unless the transaction is committed on the Mirror database, it will not be committted on the Principal Database. Truncating the transaction log file is not the solution, make sure you run t-log backup more frequently.

Did you try to execute the command suggested by PrinceLucifer, in the earlier reply? What is the output of the query?

---

SKG: Please Marked as Answered, if it resolves your issue. (b: http://sudeeptaganguly.wordpress.com)