We have sql server 2000 ,we r dong transactional replication,out distribution
agent latency is around 1000000 from last 2 days before that,it was around
4000,please suggest us the solution immediately.
Thank you.
Assuming there hasn't been a massive increase in replicated commands, have a
look for blocking issues on the subscriber when the distribution agent runs
(sp_who2).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
|||Have you verified your distribution agent is running? You also need to make
sure it is not stuck in the initializing state.
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
"Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
news:80D4C0B5-0D51-438F-8674-8E8FE9DEFAE9@.microsoft.com...
> We have sql server 2000 ,we r dong transactional replication,out
> distribution
> agent latency is around 1000000 from last 2 days before that,it was around
> 4000,please suggest us the solution immediately.
> Thank you.
|||Our distribution agent is running properly Actually there are another
subscriber where the latency is 4000,so what will be the possible reason of
increased latencyin the other server.plese reply imediately.
"Hilary Cotter" wrote:
> Have you verified your distribution agent is running? You also need to make
> sure it is not stuck in the initializing state.
> --
> 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
>
> "Rupesh Mondal" <RupeshMondal@.discussions.microsoft.com> wrote in message
> news:80D4C0B5-0D51-438F-8674-8E8FE9DEFAE9@.microsoft.com...
>
>
|||Sir Thank you for your reply
"Paul Ibison" wrote:
> Assuming there hasn't been a massive increase in replicated commands, have a
> look for blocking issues on the subscriber when the distribution agent runs
> (sp_who2).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
>
>
Showing posts with label transactional. Show all posts
Showing posts with label transactional. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
Incorrect table Definitions
I am using Transactional replication and from time to time I get an Invalid
column error on the distribution agent for either a stored procedure or view.
When I take a look at the table definition script I see that it is missing
the newest columns that were added. Does anyone else have this problem?
Does anyone know what causes this and how to fix it? The only fix I've found
so far is to drop the table from replication and add it again and produce a
new snapshot and then it seems to see the new columns.
Where are these extra columns? On the Publisher or subscriber?
It sounds like they are on the subscriber, which means someone is changing
the schema there. Make schema changes on the publisher using
sp_repladdcolumn or sp_repldropcolumn.
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
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> I am using Transactional replication and from time to time I get an
Invalid
> column error on the distribution agent for either a stored procedure or
view.
> When I take a look at the table definition script I see that it is
missing
> the newest columns that were added. Does anyone else have this problem?
> Does anyone know what causes this and how to fix it? The only fix I've
found
> so far is to drop the table from replication and add it again and produce
a
> new snapshot and then it seems to see the new columns.
|||The Extra columns are ones we added with scripts to the publisher database.
But then when we push a snapshot those new columns are not getting scripted.
I'm not sure why the replication script generator would generate the scripts
any differently than when you use Enterprise manager's script generating
tools, but they are not working the same.
"Hilary Cotter" wrote:
> Where are these extra columns? On the Publisher or subscriber?
> It sounds like they are on the subscriber, which means someone is changing
> the schema there. Make schema changes on the publisher using
> sp_repladdcolumn or sp_repldropcolumn.
> --
> 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
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> Invalid
> view.
> missing
> found
> a
>
>
|||Reply at bottom.
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...[vbcol=seagreen]
> The Extra columns are ones we added with scripts to the publisher
> database.
> But then when we push a snapshot those new columns are not getting
> scripted.
> I'm not sure why the replication script generator would generate the
> scripts
> any differently than when you use Enterprise manager's script generating
> tools, but they are not working the same.
> "Hilary Cotter" wrote:
Don't you have to mark the replication for reinitialistion prior to creating
the new snapshot? I'm still a beginner on the replication side (well, most
of SQL Server :P), but whenever I make changes to publication properties the
EM dialog always points out that the publication has to be reinitialised, so
I'd assume that for the snapshot agent to pick up changes to the schema the
same thing would need to be done.
Dan
|||Yes, you do have to reinitialize and I am doing that as well. Basically we
drop the article (table) from both the subscription and publication, then I
modify the table structure, add the table back into the publication and
subscription and then reinitialize the subscription. Then I push the new
snapshot and look at the generated files and see that the table was not
scripted with the new columns.
"Daniel Crichton" wrote:
> Reply at bottom.
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...
> Don't you have to mark the replication for reinitialistion prior to creating
> the new snapshot? I'm still a beginner on the replication side (well, most
> of SQL Server :P), but whenever I make changes to publication properties the
> EM dialog always points out that the publication has to be reinitialised, so
> I'd assume that for the snapshot agent to pick up changes to the schema the
> same thing would need to be done.
> Dan
>
>
|||"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:5EA098FA-2F35-421C-B426-038EF2AC1158@.microsoft.com...
> Yes, you do have to reinitialize and I am doing that as well. Basically
> we
> drop the article (table) from both the subscription and publication, then
> I
> modify the table structure, add the table back into the publication and
> subscription and then reinitialize the subscription. Then I push the new
> snapshot and look at the generated files and see that the table was not
> scripted with the new columns.
Oh well, that's my involvement finished then - so far I've not needed to
modify any replicated tables, and as everything is still in development I'd
likely be lazy and use EM to rebuild them anyway. Sorry.
Dan
column error on the distribution agent for either a stored procedure or view.
When I take a look at the table definition script I see that it is missing
the newest columns that were added. Does anyone else have this problem?
Does anyone know what causes this and how to fix it? The only fix I've found
so far is to drop the table from replication and add it again and produce a
new snapshot and then it seems to see the new columns.
Where are these extra columns? On the Publisher or subscriber?
It sounds like they are on the subscriber, which means someone is changing
the schema there. Make schema changes on the publisher using
sp_repladdcolumn or sp_repldropcolumn.
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
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> I am using Transactional replication and from time to time I get an
Invalid
> column error on the distribution agent for either a stored procedure or
view.
> When I take a look at the table definition script I see that it is
missing
> the newest columns that were added. Does anyone else have this problem?
> Does anyone know what causes this and how to fix it? The only fix I've
found
> so far is to drop the table from replication and add it again and produce
a
> new snapshot and then it seems to see the new columns.
|||The Extra columns are ones we added with scripts to the publisher database.
But then when we push a snapshot those new columns are not getting scripted.
I'm not sure why the replication script generator would generate the scripts
any differently than when you use Enterprise manager's script generating
tools, but they are not working the same.
"Hilary Cotter" wrote:
> Where are these extra columns? On the Publisher or subscriber?
> It sounds like they are on the subscriber, which means someone is changing
> the schema there. Make schema changes on the publisher using
> sp_repladdcolumn or sp_repldropcolumn.
> --
> 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
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:B8E2DAEF-629C-4BCD-B87B-33650C14B0B9@.microsoft.com...
> Invalid
> view.
> missing
> found
> a
>
>
|||Reply at bottom.
"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...[vbcol=seagreen]
> The Extra columns are ones we added with scripts to the publisher
> database.
> But then when we push a snapshot those new columns are not getting
> scripted.
> I'm not sure why the replication script generator would generate the
> scripts
> any differently than when you use Enterprise manager's script generating
> tools, but they are not working the same.
> "Hilary Cotter" wrote:
Don't you have to mark the replication for reinitialistion prior to creating
the new snapshot? I'm still a beginner on the replication side (well, most
of SQL Server :P), but whenever I make changes to publication properties the
EM dialog always points out that the publication has to be reinitialised, so
I'd assume that for the snapshot agent to pick up changes to the schema the
same thing would need to be done.
Dan
|||Yes, you do have to reinitialize and I am doing that as well. Basically we
drop the article (table) from both the subscription and publication, then I
modify the table structure, add the table back into the publication and
subscription and then reinitialize the subscription. Then I push the new
snapshot and look at the generated files and see that the table was not
scripted with the new columns.
"Daniel Crichton" wrote:
> Reply at bottom.
> "jencis10" <jencis10@.discussions.microsoft.com> wrote in message
> news:797A9487-C13E-4746-B594-518CA0757521@.microsoft.com...
> Don't you have to mark the replication for reinitialistion prior to creating
> the new snapshot? I'm still a beginner on the replication side (well, most
> of SQL Server :P), but whenever I make changes to publication properties the
> EM dialog always points out that the publication has to be reinitialised, so
> I'd assume that for the snapshot agent to pick up changes to the schema the
> same thing would need to be done.
> Dan
>
>
|||"jencis10" <jencis10@.discussions.microsoft.com> wrote in message
news:5EA098FA-2F35-421C-B426-038EF2AC1158@.microsoft.com...
> Yes, you do have to reinitialize and I am doing that as well. Basically
> we
> drop the article (table) from both the subscription and publication, then
> I
> modify the table structure, add the table back into the publication and
> subscription and then reinitialize the subscription. Then I push the new
> snapshot and look at the generated files and see that the table was not
> scripted with the new columns.
Oh well, that's my involvement finished then - so far I've not needed to
modify any replicated tables, and as everything is still in development I'd
likely be lazy and use EM to rebuild them anyway. Sorry.
Dan
Friday, February 24, 2012
Inactive Transaction/Log will not truncate after replication disabled
Hi, all
We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
environment. I set up a transactional replication from 'DB1' at node A (
serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at node
B. Database option for DB1 in Node A is 'full recovery' model with
scheduled transaction backup run.
I set up the push replication at 'DB1' with published article 'T' and not
yet to let the snapshot agent start to do the reinitiating, I decided to
disable/remove all replications setup via Enterprise Manager. The Enterprise
Manager did remove everything from what I can see. But from that on, I
noticed that the Transaction Log in the 'DB1' keep growing even after log
file backup, the inactive transactions are queued for future snapshot usage,
I guess. I followed some articles found to run DTS to transfer some data
over to other database or reset up the replication and run the 'EXEC
sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time = 0,
@.reset = 1'. They all did truncate the log after the action.
However, problem is that I do not want the replication any more on the
DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed the
replication/publisher setup, I can not run that 'sp_repldone' to clear the
log. But once I put back the replication/publisher for that cleanup action
and remove the replication after that, it seems to put a mark in the
database and the transaction log starts to queue and grow in a
no-replication setup. For comparison, I also did some test in a stand-alone,
non-clustered SQL server box and with publisher/distributor/subscriber all
in the same box but with two SQL Server instances to mimic the operation,
the transaction log seems to work fine and not to keep growing after
replication setup was removed.
Does anyone have the same experience or any insight to this issue ? please
help.
Thanks in advance for any of your input.
Eugene
I am a little confused by this statement "I decided to disable/remove all
replications setup via Enterprise Manager" Do you mean you disabled
replication or your dropped all subscriptions? If you dropped anonymous pull
subscriptions, it is possible that the log reader will keep reading the tlog
on DB1, and writing to the distribution database on DB2.
It seems however, from what you describe that you have disabled replication
on DB1, and still your tlog continues to grow. If this is the case, can you
run dbcc opentran in the database you are publishing? Also run sp_repltran.
sp_repltran should return nothing.
I would try to disable the publisher (db1) from being able to use the
distribution database on db2, and then enable db1 as a distributor, create a
local publication and a local subscription, and then disable publishing.
This should clear the condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on SQL FTS/Indexing Services
http://www.indexserverfaq.com
"Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hi, all
> We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
> environment. I set up a transactional replication from 'DB1' at node A (
> serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at
node
> B. Database option for DB1 in Node A is 'full recovery' model with
> scheduled transaction backup run.
> I set up the push replication at 'DB1' with published article 'T' and not
> yet to let the snapshot agent start to do the reinitiating, I decided to
> disable/remove all replications setup via Enterprise Manager. The
Enterprise
> Manager did remove everything from what I can see. But from that on, I
> noticed that the Transaction Log in the 'DB1' keep growing even after log
> file backup, the inactive transactions are queued for future snapshot
usage,
> I guess. I followed some articles found to run DTS to transfer some data
> over to other database or reset up the replication and run the 'EXEC
> sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0,
> @.reset = 1'. They all did truncate the log after the action.
> However, problem is that I do not want the replication any more on the
> DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed
the
> replication/publisher setup, I can not run that 'sp_repldone' to clear the
> log. But once I put back the replication/publisher for that cleanup action
> and remove the replication after that, it seems to put a mark in the
> database and the transaction log starts to queue and grow in a
> no-replication setup. For comparison, I also did some test in a
stand-alone,
> non-clustered SQL server box and with publisher/distributor/subscriber all
> in the same box but with two SQL Server instances to mimic the operation,
> the transaction log seems to work fine and not to keep growing after
> replication setup was removed.
> Does anyone have the same experience or any insight to this issue ?
please
> help.
> Thanks in advance for any of your input.
> Eugene
>
>
>
|||Hilary,
I did what you said to re-establish the replication setup again but have
everything ( publisher/distributor/subscriber) LOCALLY in one node instead
of across two nodes that I did before and then disable publishing. It works
perfectly to clean up all marks, the inactive transaction log is cleared up
after the backup. Everything is working now.
Thanks a lot for your prompt suggestion and want to say thanks again on your
attention on almost every post in the group.
Eugene
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e2PBQROCFHA.3840@.tk2msftngp13.phx.gbl...
> I am a little confused by this statement "I decided to disable/remove all
> replications setup via Enterprise Manager" Do you mean you disabled
> replication or your dropped all subscriptions? If you dropped anonymous
pull
> subscriptions, it is possible that the log reader will keep reading the
tlog
> on DB1, and writing to the distribution database on DB2.
> It seems however, from what you describe that you have disabled
replication
> on DB1, and still your tlog continues to grow. If this is the case, can
you
> run dbcc opentran in the database you are publishing? Also run
sp_repltran.
> sp_repltran should return nothing.
> I would try to disable the publisher (db1) from being able to use the
> distribution database on db2, and then enable db1 as a distributor, create
a[vbcol=seagreen]
> local publication and a local subscription, and then disable publishing.
> This should clear the condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on SQL FTS/Indexing Services
> http://www.indexserverfaq.com
> "Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
> news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> node
not[vbcol=seagreen]
> Enterprise
log[vbcol=seagreen]
> usage,
=[vbcol=seagreen]
> 0,
> the
the[vbcol=seagreen]
action[vbcol=seagreen]
> stand-alone,
all[vbcol=seagreen]
operation,
> please
>
We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
environment. I set up a transactional replication from 'DB1' at node A (
serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at node
B. Database option for DB1 in Node A is 'full recovery' model with
scheduled transaction backup run.
I set up the push replication at 'DB1' with published article 'T' and not
yet to let the snapshot agent start to do the reinitiating, I decided to
disable/remove all replications setup via Enterprise Manager. The Enterprise
Manager did remove everything from what I can see. But from that on, I
noticed that the Transaction Log in the 'DB1' keep growing even after log
file backup, the inactive transactions are queued for future snapshot usage,
I guess. I followed some articles found to run DTS to transfer some data
over to other database or reset up the replication and run the 'EXEC
sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time = 0,
@.reset = 1'. They all did truncate the log after the action.
However, problem is that I do not want the replication any more on the
DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed the
replication/publisher setup, I can not run that 'sp_repldone' to clear the
log. But once I put back the replication/publisher for that cleanup action
and remove the replication after that, it seems to put a mark in the
database and the transaction log starts to queue and grow in a
no-replication setup. For comparison, I also did some test in a stand-alone,
non-clustered SQL server box and with publisher/distributor/subscriber all
in the same box but with two SQL Server instances to mimic the operation,
the transaction log seems to work fine and not to keep growing after
replication setup was removed.
Does anyone have the same experience or any insight to this issue ? please
help.
Thanks in advance for any of your input.
Eugene
I am a little confused by this statement "I decided to disable/remove all
replications setup via Enterprise Manager" Do you mean you disabled
replication or your dropped all subscriptions? If you dropped anonymous pull
subscriptions, it is possible that the log reader will keep reading the tlog
on DB1, and writing to the distribution database on DB2.
It seems however, from what you describe that you have disabled replication
on DB1, and still your tlog continues to grow. If this is the case, can you
run dbcc opentran in the database you are publishing? Also run sp_repltran.
sp_repltran should return nothing.
I would try to disable the publisher (db1) from being able to use the
distribution database on db2, and then enable db1 as a distributor, create a
local publication and a local subscription, and then disable publishing.
This should clear the condition.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on SQL FTS/Indexing Services
http://www.indexserverfaq.com
"Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hi, all
> We have a clustered SQL Server 2000 (SP3a) in Window 2003 server
> environment. I set up a transactional replication from 'DB1' at node A (
> serves as 'publisher' ) to 'DB2' (as 'distributor' and 'subscriber') at
node
> B. Database option for DB1 in Node A is 'full recovery' model with
> scheduled transaction backup run.
> I set up the push replication at 'DB1' with published article 'T' and not
> yet to let the snapshot agent start to do the reinitiating, I decided to
> disable/remove all replications setup via Enterprise Manager. The
Enterprise
> Manager did remove everything from what I can see. But from that on, I
> noticed that the Transaction Log in the 'DB1' keep growing even after log
> file backup, the inactive transactions are queued for future snapshot
usage,
> I guess. I followed some articles found to run DTS to transfer some data
> over to other database or reset up the replication and run the 'EXEC
> sp_repldone @.xactid = NULL, @.xact_segno = NULL, @.numtrans = 0, @.time =
0,
> @.reset = 1'. They all did truncate the log after the action.
> However, problem is that I do not want the replication any more on the
> DB1/Node1. It is kind of a vicious cycle in the set up now. If I removed
the
> replication/publisher setup, I can not run that 'sp_repldone' to clear the
> log. But once I put back the replication/publisher for that cleanup action
> and remove the replication after that, it seems to put a mark in the
> database and the transaction log starts to queue and grow in a
> no-replication setup. For comparison, I also did some test in a
stand-alone,
> non-clustered SQL server box and with publisher/distributor/subscriber all
> in the same box but with two SQL Server instances to mimic the operation,
> the transaction log seems to work fine and not to keep growing after
> replication setup was removed.
> Does anyone have the same experience or any insight to this issue ?
please
> help.
> Thanks in advance for any of your input.
> Eugene
>
>
>
|||Hilary,
I did what you said to re-establish the replication setup again but have
everything ( publisher/distributor/subscriber) LOCALLY in one node instead
of across two nodes that I did before and then disable publishing. It works
perfectly to clean up all marks, the inactive transaction log is cleared up
after the backup. Everything is working now.
Thanks a lot for your prompt suggestion and want to say thanks again on your
attention on almost every post in the group.
Eugene
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e2PBQROCFHA.3840@.tk2msftngp13.phx.gbl...
> I am a little confused by this statement "I decided to disable/remove all
> replications setup via Enterprise Manager" Do you mean you disabled
> replication or your dropped all subscriptions? If you dropped anonymous
pull
> subscriptions, it is possible that the log reader will keep reading the
tlog
> on DB1, and writing to the distribution database on DB2.
> It seems however, from what you describe that you have disabled
replication
> on DB1, and still your tlog continues to grow. If this is the case, can
you
> run dbcc opentran in the database you are publishing? Also run
sp_repltran.
> sp_repltran should return nothing.
> I would try to disable the publisher (db1) from being able to use the
> distribution database on db2, and then enable db1 as a distributor, create
a[vbcol=seagreen]
> local publication and a local subscription, and then disable publishing.
> This should clear the condition.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on SQL FTS/Indexing Services
> http://www.indexserverfaq.com
> "Eugene Hwang" <hwang_eugene@.hotmail.com> wrote in message
> news:uxkXd9KCFHA.2288@.TK2MSFTNGP14.phx.gbl...
> node
not[vbcol=seagreen]
> Enterprise
log[vbcol=seagreen]
> usage,
=[vbcol=seagreen]
> 0,
> the
the[vbcol=seagreen]
action[vbcol=seagreen]
> stand-alone,
all[vbcol=seagreen]
operation,
> please
>
Subscribe to:
Posts (Atom)