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:

DBCC DBREINDEX

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
Cheers
If an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>

DBCC DBREINDEX

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
CheersIf an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Regards.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>

DBCC DBREINDEX

Hi,
After runing this command on a table I noticed that logical scan
fragmentation increased.
I took a snapshot of the amount of fragementation before and after using
DBCC Showcontig and compared the results.
I would have expected the logical fragmentation to descrease.
Can someone please explain why this happened and would executing DBReindex
improve it.
CheersIf an index has very low fragmentation then rebuilding it can increase the
logical scan fragmentation because the layout of pages changes. Please read
the whitepaper below for more details.
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Regards.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"mm" <postto@.news.com> wrote in message
news:uihGHJlWEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> After runing this command on a table I noticed that logical scan
> fragmentation increased.
> I took a snapshot of the amount of fragementation before and after using
> DBCC Showcontig and compared the results.
> I would have expected the logical fragmentation to descrease.
> Can someone please explain why this happened and would executing DBReindex
> improve it.
> Cheers
>
>