The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION... -- sql server replication

sql server replication : The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION...

Adam Patrick Cassidy
10/30/2006 1:39:39 PM

http://support.microsoft.com/kb/902320/en-us

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

Raymond Mak [MSFT]
10/30/2006 2:23:29 PM

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

[quoted text, click to view]

Adam Patrick Cassidy
10/30/2006 4:53:16 PM

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:)

[quoted text, click to view]

Paul Ibison
10/30/2006 8:04:13 PM

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 .

Paul Ibison
10/30/2006 10:12:50 PM

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 .

Raymond Mak [MSFT]
11/13/2006 9:33:50 AM

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
[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server replication

View Other Months
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007

home · blog · groups

Questions? Comments? Contact the dn