Replication Error

Hi ,
I have set up some push replication i.e i subscriber and multipler publisher
and i found that in one country the replication failed
it says "subscription is marked inactive and must be re-initialized"
how do i check the error ? and how i can resolved this error ?
tks & rdgsHi
http://groups.google.ch/groups?hl=en&lr=&threadm=108401c36123%24814cf5c0%24a001280a%40phx.gbl&rnum=1&prev=/groups%3Fq%3Dsubscription%2Bmarked%2Binactive%26hl%3Den%26lr%3D%26selm%3D108401c36123%2524814cf5c0%2524a001280a%2540phx.gbl%26rnum%3D1
Regards
Mike
"maxzsim" wrote:
> Hi ,
> I have set up some push replication i.e i subscriber and multipler publisher
> and i found that in one country the replication failed
> it says "subscription is marked inactive and must be re-initialized"
> how do i check the error ? and how i can resolved this error ?
> tks & rdgs|||Hi Mike,
the link that you provided is really useful
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> http://groups.google.ch/groups?hl=en&lr=&threadm=108401c36123%24814cf5c0%24a001280a%40phx.gbl&rnum=1&prev=/groups%3Fq%3Dsubscription%2Bmarked%2Binactive%26hl%3Den%26lr%3D%26selm%3D108401c36123%2524814cf5c0%2524a001280a%2540phx.gbl%26rnum%3D1
> Regards
> Mike
> "maxzsim" wrote:
> > Hi ,
> >
> > I have set up some push replication i.e i subscriber and multipler publisher
> > and i found that in one country the replication failed
> > it says "subscription is marked inactive and must be re-initialized"
> >
> > how do i check the error ? and how i can resolved this error ?
> >
> > tks & rdgs|||Hi after looking at this issue I would refer to this article to determine how
to resolve this issue.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replmon_92ge.asp
"maxzsim" wrote:
> Hi ,
> I have set up some push replication i.e i subscriber and multipler publisher
> and i found that in one country the replication failed
> it says "subscription is marked inactive and must be re-initialized"
> how do i check the error ? and how i can resolved this error ?
> tks & rdgs

Replication Error

Hi ,
I have set up some push replication i.e i subscriber and multipler publisher
and i found that in one country the replication failed
it says "subscription is marked inactive and must be re-initialized"
how do i check the error ? and how i can resolved this error ?
tks & rdgs
Hi
http://groups.google.ch/groups?hl=en...gbl%26rnum%3D1
Regards
Mike
"maxzsim" wrote:

> Hi ,
> I have set up some push replication i.e i subscriber and multipler publisher
> and i found that in one country the replication failed
> it says "subscription is marked inactive and must be re-initialized"
> how do i check the error ? and how i can resolved this error ?
> tks & rdgs
|||Hi Mike,
the link that you provided is really useful
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> http://groups.google.ch/groups?hl=en...gbl%26rnum%3D1
> Regards
> Mike
> "maxzsim" wrote:
|||Hi after looking at this issue I would refer to this article to determine how
to resolve this issue.
http://msdn.microsoft.com/library/de...plmon_92ge.asp
"maxzsim" wrote:

> Hi ,
> I have set up some push replication i.e i subscriber and multipler publisher
> and i found that in one country the replication failed
> it says "subscription is marked inactive and must be re-initialized"
> how do i check the error ? and how i can resolved this error ?
> tks & rdgs

Replication Error

Hi ,
I have set up some push replication i.e i subscriber and multipler publisher
and i found that in one country the replication failed
it says "subscription is marked inactive and must be re-initialized"
how do i check the error ? and how i can resolved this error ?
tks & rdgsHi
[url]http://groups.google.ch/groups?hl=en&lr=& threadm=108401c36123%24814cf5c0%24a00128
0
a%40phx.gbl&rnum=1&prev=/ groups%3Fq%3Dsubscription%2Bmarked%2Bina
ctive%26hl%3Den%2
6lr%3D%26selm%3D108401c36123%2524814cf5c
0%2524a001280a%2540phx.gbl%26rnum%3D1[/url
]
Regards
Mike
"maxzsim" wrote:

> Hi ,
> I have set up some push replication i.e i subscriber and multipler publis
her
> and i found that in one country the replication failed
> it says "subscription is marked inactive and must be re-initialized"
> how do i check the error ? and how i can resolved this error ?
> tks & rdgs|||Hi Mike,
the link that you provided is really useful
tks & rdgs
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> [url]http://groups.google.ch/groups?hl=en&lr=&threadm=108401c36123%24814cf5c0%24a0012
80a%40phx.gbl&rnum=1&prev=/ groups%3Fq%3Dsubscription%2Bmarked%2Bina
ctive%26hl%3Den
%26lr%3D%26selm%3D108401c36123%2524814cf
5c0%2524a001280a%2540phx.gbl%26rnum%3D1[/u
rl]
> Regards
> Mike
> "maxzsim" wrote:
>|||Hi after looking at this issue I would refer to this article to determine ho
w
to resolve this issue.
2ge.asp" target="_blank">http://msdn.microsoft.com/library/d...
2ge.asp
"maxzsim" wrote:

> Hi ,
> I have set up some push replication i.e i subscriber and multipler publis
her
> and i found that in one country the replication failed
> it says "subscription is marked inactive and must be re-initialized"
> how do i check the error ? and how i can resolved this error ?
> tks & rdgs

Replication Deadlocks

Hi All,

I need to get some assistance with some replication deadlocks im getting.

We have two servers, which push information between each other, which I have a hunch could be a cause.

Basically I keep getting the 'Agent Retry' Alert from SQL:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When doing some googleing, I found the Trace Flags and setup flags 1204 and 1205, but they dont give me any additional information.

I've looked in the error log, but all I get is this:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Does anyone have any suggestions on what I can try to find more information on the cause?

Cheers

This may be a genuine deadlock. Did it work when you restart the replication agent affected?|||If you're using SQL 2005, then use trace flag 1222, it will print out more detailed information to the errorlog. The other alternative is to capture a profiler trace using the Deadlock events.|||Hi, I've restarted and recreated the replication, but every now and again it comes back.

It's not a mega issue, but I want to resolve it.

We are running SQL 2K, sadly wont be going to 2K5 until at least SP1 to allow plenty of time to watch what's happenning with it.|||I'm doing that now, but I've already had a couple of occurances this morning which have not shown up.

Will check it over again. Thanks for the tip.|||Ok, then you can use 1204 trace flag instead. If you're restarting your server frequently, then you can just add it to the server startup trace flag section.

When you find the issue, and need help parsing it, cut/paste the output here and we can help you figure it out.
|||Thats the problem. I use Traceflag 1204, 1205, but it doesent give me any more information, than what I've already posted.

If i look through the log file, I can see the time and date where I run a DBCC TraceOn(1204, 1205), but then afterwards, when I get a replication retry/deadlock, I only get the message in the initial post...

|||

How are you enabling the trace flags? And are you enabling the trace flags on the correct machine? Try enabling them on both the publisher and subscriber machine.

You can enable them in one of two ways:

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

|||Thanks for your help so far...Much appreciated.

I was enabling them via Query Analyser. I've just re-entered your statement, so will see what I can find out on the next agent retry.

Thanks

Steve|||

Right,

I've ran the command as above, which I can see in the error log, but anything after that fails to show any more information.

This is all I get from the log:

2005-09-21 16:12:30.98 spid4 -

2005-09-21 16:16:07.60 spid188 Error: 14152, Severity: 10, State: 1

2005-09-21 16:16:07.60 spid188 Replication-Replication Distribution Subsystem: agent <SNIP>SQ01-EMS-<SNIP>SQ02\UK-183 scheduled for retry. Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

2005-09-21 16:17:56.21 spid4 -
2005-09-21 16:17:56.21 spid4 Starting deadlock search 41806

2005-09-21 16:17:56.21 spid4 Target Resource Owner:

Do the traceflags expire after a certain period of time or do the remove themselves when you close Query Analyser?

Thanks

|||

I'm not sure why the traceflag isn't working. Please use profiler instead then, use the Lock:Deadlock and Lock:Deadlock Chain events.

|||

Bringing this one back up...

I'm still having this issue. Profiler and TraceFlags give me nothing, so I've done some digging into the replications themselves.

I have a hunch that the table is being locked when data is being pushed to it, it cant because it's locked.

Is there any way I can check the type of locking happenning ?

|||Hi Steve,
I hope you enabled the trace flags with the options that Greg mentioned.

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

If you used option 2, you may need to restart your server. Also add -T 3605 if you used option 2.

And as a sidenote, would it be a case the replication agent is trying to select/insert rows while an external process (user query) is also trying to update the same table? then it could be understandable. Otherwise, without the deadlock graph, it will hard to understand what could be going on.

|||

Hi Mahesh,

I have indeed put those flags in the startup parameters section.

I have thought about what you suggested with the agent accessing whilt another process is working...

I've done a diag. of our replication: http://www.aoqz41.dsl.pipex.com/Replication.jpg

The DB Circled is the one causing problems, with the replication from 'EMS Claims Payments'. I'm wondering if this is a 'Real Deadlock' situation?

Steve

|||But after adding the trace flags to the startup paramters, did you restart the SQL server?

And looking at your diagram, I assume the line from EMS (EMS Claims Payments) is the replication agent writing data. I see only one more arrow into the UKResManClock db (circled one). Does this mean that this also writing data (through replication or user DML)? If so, that can be another thing to look at. Also would the same tables be updated by these two lines?

Also just FYI: Merge replication does not recommend a central subscriber topology. It has its problems.

Replication Deadlocks

Hi All,

I need to get some assistance with some replication deadlocks im getting.

We have two servers, which push information between each other, which I have a hunch could be a cause.

Basically I keep getting the 'Agent Retry' Alert from SQL:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

When doing some googleing, I found the Trace Flags and setup flags 1204 and 1205, but they dont give me any additional information.

I've looked in the error log, but all I get is this:

DESCRIPTION: Error: 14152, Severity: 10, State: 1

Replication-Replication Distribution Subsystem: agent SQ01-EMS-SQ02\UK-183 scheduled for retry. Transaction (Process ID 68) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Does anyone have any suggestions on what I can try to find more information on the cause?

Cheers

This may be a genuine deadlock. Did it work when you restart the replication agent affected?|||If you're using SQL 2005, then use trace flag 1222, it will print out more detailed information to the errorlog. The other alternative is to capture a profiler trace using the Deadlock events.|||Hi, I've restarted and recreated the replication, but every now and again it comes back.

It's not a mega issue, but I want to resolve it.

We are running SQL 2K, sadly wont be going to 2K5 until at least SP1 to allow plenty of time to watch what's happenning with it.|||I'm doing that now, but I've already had a couple of occurances this morning which have not shown up.

Will check it over again. Thanks for the tip.|||Ok, then you can use 1204 trace flag instead. If you're restarting your server frequently, then you can just add it to the server startup trace flag section.

When you find the issue, and need help parsing it, cut/paste the output here and we can help you figure it out.
|||Thats the problem. I use Traceflag 1204, 1205, but it doesent give me any more information, than what I've already posted.

If i look through the log file, I can see the time and date where I run a DBCC TraceOn(1204, 1205), but then afterwards, when I get a replication retry/deadlock, I only get the message in the initial post...

|||

How are you enabling the trace flags? And are you enabling the trace flags on the correct machine? Try enabling them on both the publisher and subscriber machine.

You can enable them in one of two ways:

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

|||Thanks for your help so far...Much appreciated.

I was enabling them via Query Analyser. I've just re-entered your statement, so will see what I can find out on the next agent retry.

Thanks

Steve|||

Right,

I've ran the command as above, which I can see in the error log, but anything after that fails to show any more information.

This is all I get from the log:

2005-09-21 16:12:30.98 spid4 -

2005-09-21 16:16:07.60 spid188 Error: 14152, Severity: 10, State: 1

2005-09-21 16:16:07.60 spid188 Replication-Replication Distribution Subsystem: agent <SNIP>SQ01-EMS-<SNIP>SQ02\UK-183 scheduled for retry. Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

2005-09-21 16:17:56.21 spid4 -
2005-09-21 16:17:56.21 spid4 Starting deadlock search 41806

2005-09-21 16:17:56.21 spid4 Target Resource Owner:

Do the traceflags expire after a certain period of time or do the remove themselves when you close Query Analyser?

Thanks

|||

I'm not sure why the traceflag isn't working. Please use profiler instead then, use the Lock:Deadlock and Lock:Deadlock Chain events.

|||

Bringing this one back up...

I'm still having this issue. Profiler and TraceFlags give me nothing, so I've done some digging into the replications themselves.

I have a hunch that the table is being locked when data is being pushed to it, it cant because it's locked.

Is there any way I can check the type of locking happenning ?

|||Hi Steve,
I hope you enabled the trace flags with the options that Greg mentioned.

1. dbcc traceon(1204, 1205, 3605, -1). 3605 means write to errorlog, -1 means enable for all client connections.

2. In Enterprise Manager, right-click on your server, select properties. On tab General, click on "Startup Parameters". add -T 1204 and -T 1205. Then restart your server.

If you used option 2, you may need to restart your server. Also add -T 3605 if you used option 2.

And as a sidenote, would it be a case the replication agent is trying to select/insert rows while an external process (user query) is also trying to update the same table? then it could be understandable. Otherwise, without the deadlock graph, it will hard to understand what could be going on.

|||

Hi Mahesh,

I have indeed put those flags in the startup parameters section.

I have thought about what you suggested with the agent accessing whilt another process is working...

I've done a diag. of our replication: http://www.aoqz41.dsl.pipex.com/Replication.jpg

The DB Circled is the one causing problems, with the replication from 'EMS Claims Payments'. I'm wondering if this is a 'Real Deadlock' situation?

Steve

|||But after adding the trace flags to the startup paramters, did you restart the SQL server?

And looking at your diagram, I assume the line from EMS (EMS Claims Payments) is the replication agent writing data. I see only one more arrow into the UKResManClock db (circled one). Does this mean that this also writing data (through replication or user DML)? If so, that can be another thing to look at. Also would the same tables be updated by these two lines?

Also just FYI: Merge replication does not recommend a central subscriber topology. It has its problems.

Replication Deadlocks

Hello,
I am running multiple replication publication on a SQL 2k database, with
a separate server as distribution agent, then finally push subscriptions
to multiple databases across 5 servers.
All publications use row and column filtering in them.
My question is, can I use locking hints on the selection criteria for
the row filtering?
ie. Can I specify WITH (NOLOCK) on the row filtering so that it
eliminates the majority of the blocking / deadlocks?
Thanks
Dave
*** Sent via Developersdex http://www.codecomments.com ***
You could if it was a subquery. Do you have indexes on the join
condition/filter key in your filter?
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
"Dave" <Anonymous@.devdex.com> wrote in message
news:eqm8zv1THHA.5060@.TK2MSFTNGP02.phx.gbl...
>
> Hello,
> I am running multiple replication publication on a SQL 2k database, with
> a separate server as distribution agent, then finally push subscriptions
> to multiple databases across 5 servers.
> All publications use row and column filtering in them.
> My question is, can I use locking hints on the selection criteria for
> the row filtering?
> ie. Can I specify WITH (NOLOCK) on the row filtering so that it
> eliminates the majority of the blocking / deadlocks?
> Thanks
> Dave
> *** Sent via Developersdex http://www.codecomments.com ***
|||There is an index on the originating table/column, but the deadlocking
appears to be happening on the distribution database.
David
*** Sent via Developersdex http://www.codecomments.com ***

replication conflict when use BEGIN TRANSACTION COMMIT TRANSACTION

Ok let me explain my problem
I update table abc to SERVER A
SERVER A push a replication to SERVER B
and SERVER B push to SERVER A and SERVER C
SERVER A and C are configure to replicate to SERVER B and
SERVER B is configure to replicate to SERVER A and SERVER C
(same tables are replicate)
I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
TRANSACTION
When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
error
supp info:
2 Queued update conflict. Subscriber wins. Updating Publisher ...
"rl" <rlavoie@.lgs.com> wrote in message
news:%23jnEqEGjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Ok let me explain my problem
> I update table abc to SERVER A
> SERVER A push a replication to SERVER B
> and SERVER B push to SERVER A and SERVER C
> SERVER A and C are configure to replicate to SERVER B and
> SERVER B is configure to replicate to SERVER A and SERVER C
> (same tables are replicate)
> I have no problem while I not use BEGIN TRANSACTION UPDATE... COMMIT
> TRANSACTION
> When I use BEGIN TRANSACTION UPDATE... COMMIT TRANSACTION I got a conflict
> error
>

replication and log shrinking

Since I created a replication (snapshot basic - push) when I'm running the backup log ... with truncate_ only....the size of the transaction log don't lessens. why? (sql2005 without sp)I set to simple the recovery mode and back to full....nothing
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||

check of any open transaction . DBCC Opentran

Madhu

|||thnx
I run and and this is the result:

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4990:4944:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

What can i do with this?|||

From BOL

If neither database_name nor database_id is specified, the default is the current database.

Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft? SQL Server?, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.

you need to termintate this open transaction

http://msdn2.microsoft.com/en-us/library/ms182792.aspx

http://www.sql-server-performance.com/dbcc_commands.asp

google it you will fine many

Madhu

|||I stopped the replication (snapshot) and after I could shrink the log....
So this is a bug? ( the db running without any sp)

I tried....I re-set the replication and the symptom is occurred again....
so when I set a replication on this db, I can't shrink the transaction log anymore...

Replication : Sybase - SQL Server;

Hi all,
Have anyone done the replication between Sybase and MS SQL server ?
Could you please let me know the efficient way to 'PUSH' data from Sybase to MS SQL server or 'PULL' data into MS SQL server from Sybase.
Thanks,
Hari Haran ArulmozhiThe company I used to work for used the Sybase Replication Server product to replicate data from a Sybase source to a Microsoft SQL Server target. We never had any problem with it, but it did require the Sybase Replication Server product, which was fairly pricey.

I know there were third party heterogeneous replication products out there, but most of them required modification to either the source database to add triggers or 'change tables'. That may have changed since I last looked at this at the turn of the century.|||Contact your Sybase sales rep. They'll give you a quote for Replication Server (roughly $50,000 US per sever the last that I knew), and should also offer you training options based on your location.

-PatP