daletrotman
asked on
Large Transaction Log will not shrink
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.
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.
Daniel Wilson
If setting the recovery mode to simple is acceptable, do a Backup Log with Truncate_only and a shrink after changing the recovery mode.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
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.
try to locate some "old" session, and kill it eventually (at least, make sure it commits/rolls back any open transaction.
ASKER
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.
Angel: There is no "old" session to kill unfortunately.
>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
Indeed, the transaction is a transaction waiting to be replicated.
so, you will have to either repair or kill the replication on that database
ASKER
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?
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?
start by checking if the replication jobs are running (sql server agent jobs)
ASKER
AngelIII: We have no problem with any of the replication jobs, publications or subscriptions. Everything is as it should be.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sounds to me like AngelIII's http:22794868 was the solution.
ASKER
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.