DBCC Opentran - possible causes for results

Hi to everyone,
sorry for the inadequate subject (could not find a better one), but it's
not easy to squeeze my problem into a few words...
I'm very close to opening a ticket with PSS, but before I do I wanted to
plough the field in every possible direction so to make sure I didn't
overlook any other possibilities.
Can somebody here tell me whether there are other known causes besides a
transactional replication problem that can produce the following result
when you run a DBCC OPENTRAN WITH TABLERESULTS?:
REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (41204:61:1)
I've googled quite a lot and already described and discussed this problem
in the sqlserver.replication-NG (for anybody who is interested):
*(den folgenden Link bitte in /eine/ Zeile)*
http://groups.google.de/group/micro...7c9d84c7e2cb5bd
It arises out of a transaction log that grows out of hand and can't be
shrunk in the usual way, I have to use a what I'd call steamroller tactics,
involving sp_repldone and the lot. In the end I did so last time and shrank
the log from 6,7 GB to 0,6 MB. And what I've predicted in the other thread
has come true now, I again have an open transaction. My nice small
transaction log remained so for just 3 weeks, it' s growing again. And
still I do not have any transactional replication...
Any help would be greatly appreciated.
SQLServer 2000, SP4
Thanks for reading and
Greetings from Germany
SusanneHave you tried sp_removedbreplication?
I'm assuming the database has been restored from another copy where it was
replicated, for the replication metadata to have got in ther in the first
place.
Here's a link for removedbreplication (from BOL 2005, but applies to 2000 as
well)
http://msdn2.microsoft.com/en-us/library/ms188734.aspx|||Am Mon, 27 Nov 2006 04:14:01 -0800 schrieb thomarse:

> Have you tried sp_removedbreplication?
Not yet. From what I've learned about this sp in BOL it does not make any
difference in which replication objects it removes so we would lose *all*
our objects (and there are quite a lot of them, all in use)...
But I'll have a look into this.

> I'm assuming the database has been restored from another copy where it was
> replicated, for the replication metadata to have got in ther in the first
> place.
Which would mean that at one time we had a transactional replication and
the metadata never got cleaned. Well I know you should never say never but
I really don't recall having *ever* worked with transactional replication.

> Here's a link for removedbreplication (from BOL 2005, but applies to 2000
as
> well)
> http://msdn2.microsoft.com/en-us/library/ms188734.aspx
Thanks for your help
Greetings from Germany
Susanne|||you won't lose any objects. It just removes replication information which
will hopefully stop your t-log growing.
It doesn't drop any objects.|||Am Tue, 28 Nov 2006 00:54:01 -0800 schrieb thomarse:

> you won't lose any objects. It just removes replication information which
> will hopefully stop your t-log growing.
> It doesn't drop any objects.
Are you sure about that? I've looked it up again in BOL and it says there
it deletes all replication objects in a database. I would assume that means
that all replication tables and stored procedures for replication will be
removed. As the said database is our publisher that wouldn't come too
good..
Greetings
Susanne|||I thought you said it wasn't replicated? Now you're telling me it's your
publisher? You should make your mind up.
Running sp_removedbreplication will NOT remove any of your user objects. It
ONLY removes replication metadata. You can run it with a clause, e.g. 'tran'
,
'merge' etc. if you only want to remove transactional- or merge- replication
metadata.
I think you need to reassure yourself what is actually going on with your
database, i.e. is it being replicated or not. 'Cos the signals you're sendin
g
out are somewhat mixed.
"Susanne Wenzel" wrote:

> Am Tue, 28 Nov 2006 00:54:01 -0800 schrieb thomarse:
>
> Are you sure about that? I've looked it up again in BOL and it says there
> it deletes all replication objects in a database. I would assume that mean
s
> that all replication tables and stored procedures for replication will be
> removed. As the said database is our publisher that wouldn't come too
> good..
> Greetings
> Susanne
>|||Am Thu, 30 Nov 2006 04:11:02 -0800 schrieb thomarse:

> I thought you said it wasn't replicated? Now you're telling me it's your
> publisher? You should make your mind up.
I'm really sorry if I confused you (or anybody else). That surely isn't my
intent. Where in what line did you get the impression that I was talking
about a replicated database? I've looked up all I've posted here and
couldn't find a line that would point to that conclusion. So please tell me
so next time I try to do better.
This database I'm talking about is the publisher database. It has 4
publications with some 60-odd subscribers. And it contains a lot of what
*I* would call replication objects such as special tables (for example
conflict tables) or a whole bunch of stored procedures used solely for
replicating to/with our subscribers.

> Running sp_removedbreplication will NOT remove any of your user objects. I
t
> ONLY removes replication metadata. You can run it with a clause, e.g. 'tra
n',
> 'merge' etc. if you only want to remove transactional- or merge- replicati
on
> metadata.
I looked it up again in my BOL (SQLServer 2000) and it says there you run
it on the publisher for the publisher database and on the subscriber for
the subscriber database. So I assumed that when you run it on the publisher
you among other things delete those objects I mentioned above. And the sp
has just one parameter, the name of the database.

> I think you need to reassure yourself what is actually going on with your
> database, i.e. is it being replicated or not.
Ok, I try again. Some tables are replicated *from* this database I'm
talking about *to* (better *with* as it is a merge replication) our
subscribers.

> 'Cos the signals you're sending out are somewhat mixed.
My apologies, once again I didn't intend that. I hope I've made everything
clear now.
Greetings
Susanne