Transaction Log Full

Question

Post reply

Transaction Log Full

fatinfzi

SSChasing Mays

Points: 634
More actions
March 25, 2013 at 9:52 pm

#275668

Hi,
I need help with my database. The database is mirror to another database and not the disk is full. No free space for transaction log. One of my colleague suggest me to remove the mirroring and change the recovery mode to simple. But i need the database to be mirrored to another database. Please help me on this.

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

Is the transaction log full or is it that the log grew and filled the disk?

Is the issue on the principal or on the mirror?

Do you have additional disk space on a different volume available (principal or mirror)?

what is the value of log_reuse_wait_desc in sys.databases?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

fatinfzi SSChasing Mays Points: 634 More actions · Answer 2

SQLRNNR (3/25/2013)
Is the transaction log full or is it that the log grew and filled the disk?
Is the issue on the principal or on the mirror?
Do you have additional disk space on a different volume available (principal or mirror)?
what is the value of log_reuse_wait_desc in sys.databases?

the log grew and filled the disk. It happens on the principal db. i dont have any additional space.

Can u help me to check the log_reuse_desc value?

sorry im very new in this.

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

Please read through this: http://www.sqlservercentral.com/articles/Transaction+Log/72488/

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

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 4

select log_reuse_wait_desc, name

from sys.databases

If you have the option you can add an additional log file , before that please make sure you go thru the link Gail provided. it will help fix the root cause of the issue.

Jayanth Kurup[/url]

SQLisAwe5oMe SSChampion Points: 14047 More actions · Answer 5

fatinfzi (3/25/2013)
Hi,
I need help with my database. The database is mirror to another database and not the disk is full. No free space for transaction log. One of my colleague suggest me to remove the mirroring and change the recovery mode to simple. But i need the database to be mirrored to another database. Please help me on this.

Hi, I went through similar situation recently, my log drive was getting full and when I checked the log backups, it was failing due to space also......so, to fix this issue, I took a manual log backup and shrank the log file to free up space. Sometimes you have to do more than 1 log backups in order to shrink.

So, I am not sure if you have same scenario, but it seems like log backup issue.

Also, check the mirroring monitor and see how much unsent log, etc.

Regards,

SueTons.

Regards,
SQLisAwe5oMe.