DBCC OpenTran results

Hi
Periodically my SQL 2k snapshot publication database starts to grow it's log
file without truncating.
- The recovery model is 'simple'
- There is no logreader agent, as it's snapshot only.
- DBCC OPENTRAN, returns non-zero values for non-distributed LSN, but never
returns any SPID!
All of my searching on the 'net has led me back to executing DBCC opentran,
to find the offending spid, to kill it ... but DBCC OPENtran has never
returned any Spids! it returns this sort of thing:
"Transaction information for database '<myDB>'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (202506:21682:1)
DBCC execution completed. If DBCC printed error messages, contact your
system administrator."
Dropping the publication, and shrinking the log file always works, but is a
bit extreme, and difficult to do on a production DB. This issue occurs about
once a month, with no apparent pattern that I can figure out...yet.
Any help is appreciated.
Thanks
what this means is that the log reader has not picked up some transactions
yes in the log and written them to the distribution database.
It is rare that the log reader cannot keep up, but you might want to
increase the readbatchsize (try 1000), and drop the pollinginterval to 1.
Hilary Cotter
Looking for a SQL Server replication book?
<http://www.nwsu.com/0974973602.html>
Looking for a FAQ on Indexing Services/SQL FTS
<http://www.indexserverfaq.com>
|||Thanks Hilary, but as mentioned, there is no log reader. It is snapshot
replication only. Where would I change the polling interval and
readbatchsize? The snapshot agent profile has neither of these settings, and
the distribution agent only has polling interval.
Secondly, can you shed any light on the usefulness iof the results of DBCC
OpenTran?
Thanks.
"hilary.cotter@.gmail.com" wrote:

> what this means is that the log reader has not picked up some transactions
> yes in the log and written them to the distribution database.
> It is rare that the log reader cannot keep up, but you might want to
> increase the readbatchsize (try 1000), and drop the pollinginterval to 1.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> <http://www.nwsu.com/0974973602.html>
> Looking for a FAQ on Indexing Services/SQL FTS
> <http://www.indexserverfaq.com>
>
|||oops, I missed the fact you were using snapshot replication.
For simple recovery model the tlog will be recycled when the last active
transaction is committed in the vlf (virtual log file). There are some
conditions which will prevent the tlog from being re-used and you will have
to run a bunch of large inserts into a wide table to have the log wrap
around.
So ignore my comments on the batchsize and pollinginterval.
Try to manually shrink the log using the shrink command. You can put your
database in full recover model, and backup the log to a dump device with
trunacate only, and then put it back into simple.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark J" <MarkJ@.discussions.microsoft.com> wrote in message
news:C4C4C1DA-D842-4EB9-A9FA-990858011069@.microsoft.com...[vbcol=seagreen]
> Thanks Hilary, but as mentioned, there is no log reader. It is snapshot
> replication only. Where would I change the polling interval and
> readbatchsize? The snapshot agent profile has neither of these settings,
> and
> the distribution agent only has polling interval.
> Secondly, can you shed any light on the usefulness iof the results of DBCC
> OpenTran?
> Thanks.
> "hilary.cotter@.gmail.com" wrote: