Truncate log for Mirrored Database

Question

Post reply

Truncate log for Mirrored Database

Viewing 6 posts - 16 through 20 (of 20 total)

You must be logged in to reply to this topic. Login to reply

sacha.bessard Old Hand Points: 370 More actions · Answer 1

It's possible : to shrink transaction file, backup must be performed as there are actives Virtual Log File :

http://www.xoowiki.com/Article/SQL-Server/tronquer-journal-de-log-sur-base-en-miroir-499.aspx

mahesh.shinde SSC Enthusiast Points: 148 More actions · Answer 2

You can follow below steps

1. Run below script against db in mirroring on principle server.

DECLARE @DatabaseName VARCHAR(50);

SET @DatabaseName = 'Mirrored DB Name' --> Replace the mirror db name here.

SELECT name, recovery_model_desc, log_reuse_wait_desc

FROM sys.databases

WHERE name = @DatabaseName

2. If output column log_reuse_wait_desc is saying LOG_BACKUP then simply take multiple time log backup of mirror database. Mostly 3 continuous T-log backup would enable db to shrink the log file with following command.

DBCC Shrinkfile(2,1024)

Above steps would fix the issue.

Thanks,

Mahesh Shinde

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

mahesh.shinde (5/26/2013)
2. If output column log_reuse_wait_desc is saying LOG_BACKUP then simply take multiple time log backup of mirror database. Mostly 3 continuous T-log backup would enable db to shrink the log file with following command.
DBCC Shrinkfile(2,1024)

You can't take log backups of a mirror database, the database is in the restoring state. Nor can you shrink the log of a mirror database (plus shrinking to 1MB is a poor recommendation).

Log backups and shrinks can only be done on the principal database of a mirroring configuration.

p.s. 5 year old thread.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mahesh.shinde SSC Enthusiast Points: 148 More actions · Answer 4

Yes database which is residing on principle server. You've to take T-log backup multiple time to enable database for shrinking T-log file.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

mahesh.shinde (5/26/2013)
Yes database which is residing on principle server. You've to take T-log backup multiple time to enable database for shrinking T-log file.

That's the principal database, not the mirror. You cannot take log backups of the mirror, only of the principal, and if you're got a proper log backup strategy you will not need to take multiple log backups. Shrinking the log is something that should be done with caution and never just a blanket shrink to 1MB without any thought.

p.s. 5 year old thread with the OP's problem solved 5 years ago.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mahesh.shinde SSC Enthusiast Points: 148 More actions · Answer 6

By mistake i mentioned mirror database. The steps mentioned would be executed on principle server on principle database. In case of T-log drive running out of disk space you've to shink the T-log file to avoid outage to application.

I do not think any issue in shrinking log back to 1024 MB = 1 GB size. As per your requirement you can change log file size.