Large Transaction Log will not shrink

Posted on 2008-10-23
Last Modified: 2008-11-01
Our database transaction log has grown from 500MB to 9Gb very quickly and we are now running out of disk space.

We have tried Backup Log with Truncate_only
also DBCC shrinkfile (log_file_name,1000)

We read about a method of solving this by detaching the database however this database is a publisher in merge replication and we get an error message back.

We have set the recovery model from Full to Simple with no joy.

We have tried stopping the SQL Server and renaming the Log file with no success.

We have tried restarting the Server with no success.

We are using SQL 2005 SP2 on a dell quad core 8Gb Ram server using Windows 2003 OS.

DBCC OPENTRAN returns message
Transaction information for database databasename.

Replicated Transaction Information:
        Oldest distributed LSN     : (0:0:0)
        Oldest non-distributed LSN : (1298820:6778:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

...indicating uncommitted replication transactions I believe.
Question by:daletrotman
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22789062
If setting the recovery mode to simple is acceptable, do a Backup Log with Truncate_only and a shrink after changing the recovery mode.
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22790546
the backup log will not be effective, as there IS some transaction open.
try to locate some "old" session, and kill it eventually (at least, make sure it commits/rolls back any open transaction.

Author Comment

ID: 22794054
Daniel: I have tried these suggestions (as detailed in the post). As Angelll has noted these will not work.

Angel: There is no "old" session to kill unfortunately.
Free Webinar: AWS Backup & DR

Join our upcoming webinar with experts from AWS, CloudBerry Lab, and the Town of Edgartown IT to discuss best practices for simplifying online backup management and cutting costs.

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22794677
>Angel: There is no "old" session to kill unfortunately.
Indeed, the transaction is a transaction waiting to be replicated.

so, you will have to either repair or kill the replication on that database

Author Comment

ID: 22794757
sp_replcmds results...
database:    databasename-v6-01      
replicated transactions:     2  
replication rate trans/sec:     0  
replication latency (sec):     263220.2  
replbeginlsn:    0x0013D18400001A7A0001            
replnextlsn:      0x0013D18400001AA80001

the latentcy equals about 72+ hours which is approximately when our troubles started.

Any suggestions on how to repair the merge/snapshot replication on the database?
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22794868
start by checking if the replication jobs are running (sql server agent jobs)

Author Comment

ID: 22794945
AngelIII: We have no problem with any of the replication jobs, publications or subscriptions. Everything is as it should be.

Accepted Solution

daletrotman earned 0 total points
ID: 22811265
This problem has now been solved.
Our replication publisher was publishing to 2 separate subscription servers, a LIVE server and a backup/test server. After removing the subscriptions to the backup/test server the open/uncommitted tranactions were released/deleted. Using dbcc opentran then showed us "No open transactions". The dbcc shrinkfile command then worked successfully.

Case closed. On to new problems..!!
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22811665
Sounds to me like AngelIII's http:22794868 was the solution.

Author Comment

ID: 22811740
Daniel: Fortunately we did not need to kill replication from either subscriber or the publisher. Simply remove the subscriptions from one of the subscribers. Replication remained intact throughout the process.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can I find this data? 3 28
Options for Linking SQL tables to Access 2013 9 43
Problem Backingh Up Transaction Log on Old SQL000 Database 3 26
SQL Help 4 18
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question