"Susanne Wenzel" <wirdnichtgelesen@onlinehome.de> wrote in message
news:1vgtzp4evgxgg.dlg@ID-301016.user.individual.de...
> Hi to everyone,
>
> our transaction log (again) has reached an unbelievable size (about 4,6
> Gb,
> the corresponding database has about 200 Mb). We do a backup every night
> (full recovery model). Our system: SQL Server 2000 SP4 (publisher and
> distributor identical, the publisher itself has no subscriptions), Windows
> Server 2000 SP4, several (trusted) domains, clients Win2k Pro SP4. We have
> snapshot and merge replication, definitely *no transactional replication*.
> We never had *any* transactional replication at any given time, because
> right from the beginning this type was no option for our scenario so we
> never even tested it.
>
> The common way with DBCC Shrinkfile, DBCC Loginfo, backup log and the like
> is not successful, I get the following message:
>
> 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.
>
> I know one way that is suggested (with sp_repldone and so on) to solve
> this
> problem and reduce the size of the log. We had the same problem before and
> used the repldone-thing successfully, but I'd like to avoid that for now.
> For it seems to me this way is only temporary because the log will be
> building up again, as I experience now.
>
> Our situation is best described here:
>
> *(den folgenden Link bitte in /eine/ Zeile)*
> http://groups.google.de/group/comp.databases.ms-sqlserver/browse_frm/thread/23ca52b9df791e59/9ee4e7d0f089ab98?lnk=st&q=transaction+log+replication+merge&rnum=5#9ee4e7d0f089ab98
>
> So far I've got the impression that people think, this problem can only
> occur if you use transactional replication but again I assure we have none
> of that!
>
> This thread also fits very neatly to my problem:
>
> *(den folgenden Link bitte in /eine/ Zeile)*
> http://groups.google.de/group/microsoft.public.sqlserver.replication/browse_frm/thread/456d4407ad85ede5/7b942db4b21d44cf?lnk=st&q=transaction+log+replication+merge&rnum=10#7b942db4b21d44cf
>
> It looks like the poster opened a PSS-Ticket, is there any chance to learn
> about the state of this ticket?
>
> I've invested quite a few hours to get nearer to the problem. I ran a DBCC
> OPENTRAN WITH TABLERESULTS and got the following back:
>
> REPL_DIST_OLD_LSN (0:0:0)
> REPL_NONDIST_OLD_LSN (41204:61:1)
>
> I've found another thread that deals with this problem:
>
> *(den folgenden Link bitte in /eine/ Zeile)*
> http://groups.google.de/group/microsoft.public.sqlserver.replication/browse_frm/thread/6972cf1f0c26f0b/47e34c0887db4e8b?lnk=st&q=REPL_NONDIST_OLD_LSN&rnum=1#47e34c0887db4e8b
>
> (At least I can tell that we are not running DataMirror)
>
> My aim/wish is to get this pending transaction going so the log can shrink
> on its own accord.
>
> A dbcc log gave me among a *huge* amount of other entries a BEGIN_XACT,
> but
> no corresponding COMMIT_XACT back (as I remember it from my home right
> now,
> but I can look it up another time). The usual way to end an open
> transaction as I understand, is to get the SPID and then use KILL, but
> this
> is getting me nowhere as I'm not getting a spid back. And of course we
> have
> no log reader agent showing up in EM that we could start manually.
>
> I'd really like to know what this is all about, I don't understand what's
> going on and I'd love to!
>
> Thanks for reading, any help is greatly appreciated.
>
> Greetings from Germany
> Susanne

"Susanne Wenzel" <wirdnichtgelesen@onlinehome.de> wrote in message
news:1dcmut1dj2fex$.dlg@ID-301016.user.individual.de...
> Hi Adam,
> Am Thu, 12 Oct 2006 09:29:04 -0400 schrieb Adam Patrick Cassidy:
>
>> Can you reply with the results from running the following query in the
>> database you are having problems with:
>> SELECT * FROM syspublications
>
> yes, I can do that but as I had a day off today this will not be before
> tomorrow evening. Could you tell me in advance what kind of entry/entries
> you expect?
>
> Just out of curiosity...
>
> Greetings
> Susanne

> Can you reply with the results from running the following query in the
> database you are having problems with:
> SELECT * FROM syspublications

Susanne Wenzel wrote:
> Hi Adam,
> Am Thu, 12 Oct 2006 09:29:04 -0400 schrieb Adam Patrick Cassidy:
>
> > Can you reply with the results from running the following query in the
> > database you are having problems with:
> > SELECT * FROM syspublications
>
> And here it goes (I've only included columns that had a value, since there
> are so many columns in the resultset):
>
> description MyDescription
> name MyName
> pubid 1
> repl_freq 1
> status 1
> sync_method 0
> independent_agent FALSE
> immediate_sync FALSE
> enabled_for_internet FALSE
> allow_push TRUE
> allow_pull TRUE
> allow_anonymous FALSE
> immediate_sync_ready FALSE
> allow_sync_tran FALSE
> autogen_sync_procs FALSE
> retention 0
> allow_queued_tran FALSE
> snapshot_in_defaultfolder TRUE
> post_snapshot_script PathAndNameOfMySnapshotScript.sql
> compress_snapshot FALSE
> ftp_port 21
> ftp_login anonymous
> allow_dts FALSE
> allow_subscription_copy FALSE
> conflict_retention 14
> backward_comp_level 10
>
> I hope you can read this properly.
>
> BTW I have to correct 2 points of my opening posting:
>
> 1.
> My Publisher&Distributor has subscriptions, all of them are internal
> between databases of the Publisher&Distributor, all of them snapshot
> replications.
>
> 2.
> We do not do a backup of the transaction log every night but only once a
> week (which was very astonishing to me, I remembered otherwise). I checked
> this up today.
>
> The situation is starting to become alarming now, because the transaction
> log is now so huge, it costs us all of our harddrive space which is not
> tolerable. So I think I have to do the repldone-thing out of pure necessity
> although I hate that. Which also means that I can forget about the
> PSS-Ticket because I assume the size of our transaction log will turn back
> to normal for a while after I've done all those steps.
>
> The thing that puzzles me right now is the size of the backup-file of that
> particular database (produced through a maintenance plan which consists of
> a backup of the database and does *not* contain a backup of the transaction
> log, we do a backup of that by a separate maintenance plan).
>
> It so happens that the size of this backupfile is nearly (close to 100%)
> the size of the database-file *plus* the size of the logfile which is very
> irritating to me (and that backupfile of course has also reached a size
> that is really ridiculous).
>
> We've got several other databases on that server, *all* of them have their
> backup by the same maintenance plans. And in *all other cases* the backup
> file fits roughly to the size of the database file alone as I would expect
> it.
>
> This is another thing I can't understand. I tried to find something about
> database file sizes in Kalen Delaney's book but wasn't fortunate. I will
> search the net, see if I can find something there about this second strange
> thing.
>
> My overall opinion is that there is some kind of bug involved but what can
> one really do? You have to keep the system going there is no other
> choice...
>
> Thanks for your help and
> Greetings from Germany
> Susanne