Monday, October 02, 2006 11:52 AM
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.
Tuesday, October 03, 2006 6:17 AM
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.
Thursday, October 05, 2006 7:22 PM
Thank you for the assist on this. The log file has shrunk down and propgated the same to the mirror using your suggestion.
Friday, March 14, 2008 2:23 AMHi 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.
Friday, March 14, 2008 4:06 AMModeratorHans,
You need to take transaction log backup using the below command,
Backup log database name to disk='D:\Sample\Dbname.trn'
Use database name
Monday, March 17, 2008 4:06 AM
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.
Monday, March 17, 2008 4:12 AMModeratorIs 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.
Monday, March 17, 2008 4:34 AM
Monday, March 17, 2008 11:47 PMModerator
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.
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.