The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

Post by Adam Patri » Wed, 01 Nov 2006 03:39:39


http://www.yqcomputer.com/

This article is titled "The Snapshot Agent fails and a "The COMMIT
TRANSACTION request has no corresponding BEGIN TRANSACTION" error message
may be logged in the Snapshot Agent log in SQL Server 2000" and is exactly
the problem that I have. However, the solution does not consistently
resolve my problem, nor have I had any success on getting the problem
resolved reliably.

Usually what happens is that when I get this failure message, I wait until
the nightly backup job finishes and then attempt to replicate them again and
after a few attempts it will succeed without me changing anything.

Does anyone out there have a reliable solution to this problem that I can
put into place when I get these sporadic irritating problems? Thanks in
advance.

Adam P. Cassidy
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

Post by Paul Ibiso » Wed, 01 Nov 2006 05:04:13

Adam,
I know that you know about replication so this is probably not relevant but
do you have anonymous subscribers or is this snapshot replication? If not
then I'd disable the snapshot agent. If you do need to initialize often,
then a nosync initialization will avoid this processing path directly.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

Post by Adam Patri » Wed, 01 Nov 2006 06:53:16

Around 2PM today I re-scheduled my nightly 10PM backup to start at 2PM. When
it finished, I started the snapshot again and it failed. When I restarted
it succeeded....

As I mentioned before, I've been using this personal solution for about 2
years now since I have never really understood the steps in the KB article
where it says "backup the publication" and this has always gotten me where I
needed to be.

Paul, I'm using transactional replication and your solution of doing a
backup and restore would cause me to take this database offline which I
rarely do when setting up replication since they are always small. I don't
need to initialize hardly ever, I'm getting this error on the very first
attempt at running the snapshot agen. I do have to stop content from going
to their websites when I replicate larger databases.

Any chance this is something that is looked at? I'd be happy to learn more
about this as it's been a problem for years:)
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

Post by Paul Ibiso » Wed, 01 Nov 2006 07:12:50

Adam,
I'll ask if a hotfix is on its way, and will reply to this thread when I
find out.
Just looking more at it, presumably you have a remote distributor? Is there
any reason you know of that the connection is dropped?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

Post by Raymond Ma » Wed, 01 Nov 2006 07:23:29

Adam, if KB902320 indeed applies to your scenario, it would appear that
devising a long term solution depends entirely on understanding why the
snapshot agent connection was broken from time to time in the first place
(and it may well be something that we can tweak in our code). You may also
want to consider upgrading to SQL2005 which has better support for
initializing subscription using backup restore and a new non-DMO based
snapshot agent code base which may tell you exactly why the connection gets
broken from time to time (or perhaps not subject to the same kind of random
breaking of connections)

-Raymond
 
 
 

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

Post by Raymond Ma » Wed, 15 Nov 2006 02:33:50

Adam, if you haven't done so already, I would encourage you to open a QFE
request with Microsoft Support so we can at least change the snapshot agent
code to disable AutoReconnect and hopefully the underlying connection error
will bubble up for further diagnosis. I can't promise that there is anything
we can do to address the underlying problem, but at least we have a fighting
chance as long as the real issue is not masked by the snapshot agent's
inappropriate use of the AutoReconnect setting.

Thanks much.

-Raymond