dbcc opentran results

[Larry Myers]

I have a database with a large (and growing) transaction log. Running
dbcc opentran yields the following two rows:

REPL_DIST_OLD_LSN (0:0:0)
REPL_NONDIST_OLD_LSN (508734:17171:1)

A search through BOL and the news groups yields no information on the
meaning of these values. The database is published nightly using
snapshot replication. Any help interpreting these values would be
greatly appreciated. My goal is to truncate the log back to a more
reasonable size. Thanks.

[Hilary Cotter]

it is very rare to see the REPL_NONDIST_OLD_LSN value.

What it means is that 1) your log reader agent is stopped, 2) your log
reader agent is way behind.

What is the status of your log reader agent?

Can you also run this in your publication database?

DBCC traceon(3604)

DBCC log(databasename)

DBCC traceoff

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

"Larry Myers" <lmy...@swinformatics.com> wrote in message
news:73e8147a.04081...@posting.google.com...

[Hilary Cotter]

one more thing, are you running DataMirror?

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

"Larry Myers" <lmy...@swinformatics.com> wrote in message
news:73e8147a.04081...@posting.google.com...

[Larry Myers]

Thanks for the prompt reply.

I am confused. We are using snapshot replication. I wasn't aware that a
log reader agent was part of the snapshot replication implementation.
There are no log reader agents showing in the replication monitor.

I ran the dbcc log(database) command against the publication database.
It returned a huge number of rows, too many to include here. Is there
something specific you are looking for?

Thanks again.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Hilary Cotter]

I missed that fact that you are using snapshot replication. The log reader
is not used in snapshot replication, as you so kindly point out.

What the traceflag will allow you to do is to get an idea of where your log
reader is at in reading the transaction log, i.e. how many more rows it has
to read, or how far behind it is.

Again with snapshot replication it won't really tell you anything.

you can take the value 508734:17171:1, convert it to hex

0007C33E:00017171:0001 and see where this hex value is in the Current LSN of
the results set returned from the trace flag.

It is possible that this corresponds to an open transaction which you might
want to kill. DBCC opentran should give you the spid for this. You might
also want to review some of the kb articles on shrinking the transaction
log, ie

http://support.microsoft.com/default.aspx?scid=kb;en-us;272318&Product=sql2k

http://support.microsoft.com/default.aspx?scid=kb;EN-US;256650

--
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html

"Larry Myers" <lmy...@swnformtics.com> wrote in message
news:%2309dhrh...@TK2MSFTNGP10.phx.gbl...