Shrink the log file

Question

Shrink the log file

sanya.bandaru 52584

SSC Rookie

Points: 36
More actions
February 8, 2010 at 10:35 pm

#138733

I have database in production Server which replication(transaction replication) is in use. The transaction log was set to restricted mode to 20GB. some transaction are going on that database and the transaction log was full and the replication was also failed due to Tlog was full. When im truncating the Tlog and shrink the log is not working due to the transactions are going on. So can anybody provode me the solution how to shrink the log with out killing that tranactions and without adding a new log file...............

Viewing 7 posts - 1 through 7 (of 7 total)

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

Suresh B. SSC-Insane Points: 22986 More actions · Answer 1

1) Run DBCC OPENTRAN and see what are the open transactions.

2) I don't think replication is failing due to Tlog full. Confirm the reason for replication failure. Replication has log reader agent. It is supposed to read the log, not create any open transaction.

3) If replication starts working, everything should be fine. Fix the failing replication.

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 2

Suresh B. (2/8/2010)
1) Run DBCC OPENTRAN and see what are the open transactions.
2) I don't think replication is failing due to Tlog full. Confirm the reason for replication failure. Replication has log reader agent. It is supposed to read the log, not create any open transaction.
3) If replication starts working, everything should be fine. Fix the failing replication.

If you can't add any new data to the log, then there will be nothing new for the log reader agent to read. Replication is bound to "fail" because of that.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

sanya.bandaru 52584 (2/8/2010)
I have database in production Server which replication(transaction replication) is in use. The transaction log was set to restricted mode to 20GB. some transaction are going on that database and the transaction log was full and the replication was also failed due to Tlog was full. When im truncating the Tlog and shrink the log is not working due to the transactions are going on. So can anybody provode me the solution how to shrink the log with out killing that tranactions and without adding a new log file...............

Do you backup your transaction log?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

muthukkumaran Kaliyamoorthy SSCoach Points: 19026 More actions · Answer 4

sanya.bandaru 52584 (2/8/2010)
I have database in production Server which replication(transaction replication) is in use. The transaction log was set to restricted mode to 20GB. some transaction are going on that database and the transaction log was full and the replication was also failed due to Tlog was full. When im truncating the Tlog and shrink the log is not working due to the transactions are going on. So can anybody provode me the solution how to shrink the log with out killing that tranactions and without adding a new log file...............

Read the following URL:

Managing Transaction Logs By Gail Shaw[/url]

Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/

sanya.bandaru 52584 SSC Rookie Points: 36 More actions · Answer 5

There are no oldest tranactions are going. The replication failed due to the tlog full error.

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 6

That's understood, but you have not responded to Jeff's question earlier, do you take T-log backups regularly?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty