Transaction log not shrinking since Mirrioring Started

Question

Hello all,

I am currently having a rather pestering issue with my full/transaction log backups. It seems that after running either, my logs do not truncate and the file continues to stay @ the current size and eventually fill to the brim. This issue only began after mirroring was setup. Is there any differences in log file maintainence when dealing with Mirrored databases ? I made my last attempt to run backups and waited over the weekend for some hope that a checkpoint would occur and my file would be shrunk once again for normal use.

 

Any insight on this is greatly apprecieated.

 

Answer 1

Hi Anthony,

Database mirroring is only supported under the full recovery model and in order to be able to truncate the log under this model, you need to perform a checkpoint of the database (see the CHECKPOINT statement) and then perform a log backup (WITHOUT specifying the NO_TRUNCATE or NO_LOG options). For detailed description of log truncation you may want to refer to the topic "Truncating the Transaction Log" in SQL Server Books Online.

Also, make sure that the mirroring session you established is not suspended and the partners are able to communicate. The log on the principal cannot be truncated until it's been sent to the mirror.

Let me know if this worked out for you.

-- Kaloian.

Answer 2

Thank you for the assist on this. The log file has shrunk down and propgated the same to the mirror using your suggestion.

 

Thanks again

 

Anthony

Answer 3

Hi guys , I am facing this problem also. Can list out the way for truncating the log during mirroring (together with T-SQL). I am kinda newbie on it. Hope can get any assistance here again. Thank you.

Best Regards,
      Hans

Answer 4

Hans,

You need to take transaction log backup using the below command,

Code Snippet

Backup log database name to disk='D:\Sample\Dbname.trn'

and then shrink the database using dbcc shrinkfile command as shown below,

Code Snippet

Use database name

GO

DBCC shrinkfile(dbname_log,1024)

I have seen the above mentioned procedure works perfectly for mirrored databases as well.

- Deepak

Answer 5

 

Hi Deepak, I just tried out the solution that given by you. For the log file size is small , I am able to do so . However, my db's log size = 33 GB, it seems doesn't work.

 

Answer 6

Is there any open transactions running in the principal database ? You can find that using DBCC OPENTRAN. If any Bulk inserts etc are running continuosly the log file will not shrink as you expect it to do.

- Deepak

Answer 7

 

I just run DBCC OPENTRAN and it shows 'No active open transactions'. Is it bcoz of the log file size too large till can't shrink. But i saw my mirroring shows 'SYNCHRONIZING' status.

Answer 8

Hi Hans,

 

Synchronizing states that all the transactions are replicated to the mirror server.. Once synchronizing gets completed you can try shrinking the log file. You can execute the below query to find whether the log file is in use.

 

DBCC LOGINFO('dbname')

 

If the stauts of the output is 2 then it states that your log file is in use, once its synchronized this filed should become 0 and then try shrinking as Deepak described above.