Cannot Truncate Transaction Log!

My Transaction Log on a production database has grown to 5 GB. And
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!

Cannot Truncate Transaction Log!

My Transaction Log on a production database has grown to 5 GB. And
all but 118 MB is used in that log. DBCC Opentran shows the
following:
Replicated Transaction Information:
Oldest distributed LSN : (10576:2240:4)
Oldest non-distributed LSN : (0:0:0)
Replication is not set up on that server! There are no agents running
on it, there is no Distribution database. But it looks like something
is flagged for replication. I know a very large DTS was executed on
the database and maybe something got mixed up, and now the database
thinks it has to replicate this giant transaction.
I am shipping the logs of this database from MI to VA, and I really
don't want to break log shipping by fixing this problem. I may have
to anyway. How can I get this transaction to get cleaned out' I
can't execute sp_repldone, because no replication agents are running.
Help!!!!!!Try these links :-
http://www.support.microsoft.com/?id=256650 INF: How to Shrink the SQL
Server 7.0 Tran Log
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
http://www.support.microsoft.com/?id=272318 INF: Shrinking Log in SQL
Server 2000 with DBCC SHRINKFILE
http://www.support.microsoft.com/?id=317375 Log File Grows too big
http://www.support.microsoft.com/?id=110139 Log file filling up
http://www.mssqlserver.com/faq/logs-shrinklog.asp Shrink File
http://www.support.microsoft.com/?id=315512 Considerations for Autogrow
and AutoShrink
HTH
Ryan Waight, MCDBA, MCSE
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:978604d4.0403040636.446e8010@.posting.google.com...
> My Transaction Log on a production database has grown to 5 GB. And
> all but 118 MB is used in that log. DBCC Opentran shows the
> following:
> Replicated Transaction Information:
> Oldest distributed LSN : (10576:2240:4)
> Oldest non-distributed LSN : (0:0:0)
>
> Replication is not set up on that server! There are no agents running
> on it, there is no Distribution database. But it looks like something
> is flagged for replication. I know a very large DTS was executed on
> the database and maybe something got mixed up, and now the database
> thinks it has to replicate this giant transaction.
> I am shipping the logs of this database from MI to VA, and I really
> don't want to break log shipping by fixing this problem. I may have
> to anyway. How can I get this transaction to get cleaned out' I
> can't execute sp_repldone, because no replication agents are running.
> Help!!!!!!|||Unfortunately, the log cannot be shrunk, because shrinking only clears
up free space on the log, and what I have is something that is showing
up as used space. This makes it impossible to shrink, until I've
unflagged this transaction.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Laura
Run DBCC Loginfo(db_id)
There is a column is called 'status' .If you see the value=2 of this column
at the end ,so you have running(active) transaction and therefore you will
not able to shrink the log.
Run dummy inserts to move the status=2 on the top of the table.
"Laura Jones" <jonesl@.ofllc.com> wrote in message
news:uo$gJGgAEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Unfortunately, the log cannot be shrunk, because shrinking only clears
> up free space on the log, and what I have is something that is showing
> up as used space. This makes it impossible to shrink, until I've
> unflagged this transaction.
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!

Cannot shrink transaction log

Hi,

I have a t-log on one database which is 400mb when the database size is only 30mb. dbcc shrinkfile does not work and dbcc opentran shows no open transactions. When I tried to do a Backup log with truncate_only the following message displays:

The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

The database in question is not a publisher and only receives 5 subscribed articles, none of which are changed very often. The database operates 24 x 7.

Does anyone have a solution to this dilemma?

Thank YouHave you enabled the transaction checkpoint ? so that it will reduce automatically ?

dave|||Run this script (courtesy of SQL Server Professional magazine)

Sometimes a piece of data blocks the rest of the transaction log from being truncated. This script will solve your problem by created and removing garbage data until the active portion of the log loops to the beginning of the t'log.