How to reduce the size of the t-log file of a mirrored DB that is still in restoring mode

Question

How to reduce the size of the t-log file of a mirrored DB that is still in restoring mode

sqlnickxu

SSCertifiable

Points: 5026
More actions
June 2, 2013 at 7:50 pm

#278128

Hello gents
This might look like an oddball question, but one mirrored DB on our DR box has a huge t-log file that is occupying way too much space and my boss is asking me to reduce it.
Has anyone encountered the same kind of situations before?
I am a bit reluctant to shrink the PROD side ( hopefully and theoretically the t-log shrink action will be propagated to the DR?).
And way to accomplish this purely on DR?
Thanks in advance for any inputs!
Bazinga!

Viewing 6 posts - 1 through 5 (of 5 total)

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

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 1

Well, since no one bothers to answers this question.

I posted my only solution here: I have to shrink the t-log file on the principal partner of this mirroring pair on the PROD side and the mirrored DB's t-log file size was reduced shortly afterwards.

Bazinga!

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

Patience.

We're volunteers, we all have our own jobs, our own problems and we post in our spare time. If you want immediate solutions hire someone, it's not as if everyone is awake, online and eager to help at 4AM GMT

And yes,you shrink the log on the principal if you absolutely have to.

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

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 3

GilaMonster (6/3/2013)
Patience.
We're volunteers, we all have our own jobs, our own problems and we post in our spare time. If you want immediate solutions hire someone, it's not as if everyone is awake, online and eager to help at 4AM GMT
And yes,you shrink the log on the principal if you absolutely have to.

Apology for my wording, i meant no offence to any member on this site, especially warm hearted jedi masters of sql server:-P

As i was writing my original post, i was kinda in a hurry to fix that disk space problem, just wanted a quick alternative other than shrinking log on prod server which is a bit sensitive for changes.

Thanks again GilaMonster for your reply!

Bazinga!

Gerard Silveira Ten Centuries Points: 1106 More actions · Answer 4

Can you shed some light on why the transaction log was huge in the first place ?

Also wont it grow back to its original size once the effects of the shrinking of the transaction log wear off ?

sqlnickxu SSCertifiable Points: 5026 More actions · Answer 5

Gerard Silveira (6/3/2013)
Can you shed some light on why the transaction log was huge in the first place ?
Also wont it grow back to its original size once the effects of the shrinking of the transaction log wear off ?

I speculate the principal side's t-log was increased due to some heavy workload and the regular t-log backup job didn't pick up quickly enough to "recycle" the log file in a timely fashion. And then the mirrored DB at the partner also expanded its t-log file in tandem.

Bazinga!