Help! Trans log is growing and Log Reader can't connect

I have a SQL 2000 box with latest service pack as a publisher and
distributor. The log reader agent is not able to connect and as a result the
transaction log is growing.
On the other hand, there is a connection to the distrubtion database and it
appears to be an orphan connection from the log reader agent. I have killed
the orphan connection several times but that has not resolved the issue.
Following is the error I am getting:
SPID 30: transaction rollback in progress. Estimated rollback completion:
100%. Estimated time remaining: 0 seconds.
I believe if I can kill the connection, I will be able to start the log
reader agent successfully. I had the exact same issue yesterday and rebooting
the server did the trick. It is a production box so I can't afford to reboot
everyday like this.
What happens when you do a sp_repltrans in publisher database? You need to
set up a verboselevel to 2 and see what kind of output you get in output
file. That would be the start.
"Adam" wrote:

> I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and it
> appears to be an orphan connection from the log reader agent. I have killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and rebooting
> the server did the trick. It is a production box so I can't afford to reboot
> everyday like this.
>
|||it probably is a phantom. See if you can start up the log reader agent
again.
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
"Adam" <Adam@.discussions.microsoft.com> wrote in message
news:C47F8C71-4D3A-4823-84AE-1948DAFAF850@.microsoft.com...
>I have a SQL 2000 box with latest service pack as a publisher and
> distributor. The log reader agent is not able to connect and as a result
> the
> transaction log is growing.
> On the other hand, there is a connection to the distrubtion database and
> it
> appears to be an orphan connection from the log reader agent. I have
> killed
> the orphan connection several times but that has not resolved the issue.
> Following is the error I am getting:
> SPID 30: transaction rollback in progress. Estimated rollback completion:
> 100%. Estimated time remaining: 0 seconds.
> I believe if I can kill the connection, I will be able to start the log
> reader agent successfully. I had the exact same issue yesterday and
> rebooting
> the server did the trick. It is a production box so I can't afford to
> reboot
> everyday like this.
>

HELP! Subscription obsolete and Data NOT sychronized!

We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasn=B4t detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
Andreas, you could manually synchronize the data using datacompare from
Redgate then do a nosync initialization.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul Ibison schrieb:

> Andreas, you could manually synchronize the data using datacompare from
> Redgate then do a nosync initialization.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
But SQL DataCompare can either copy from database A to B or from B to
A, but cannot merge, or am I wrong?
And I a dataset exists on site A, but not on B, does that mean, that
the dataset was new on A and should copy to B, or was the dataset
delete on B and should also be deleted on A?
|||there is no way in Merge, there is a way in transactional but its dangerous.
If you do reinitialize it will prompt you to upload changes from the
subscriber first which will send the subscribers changes to the publisher.
I think you need to investigate 1) what is causing the problem which makes
your subscriber go offline 2) what is causing these errors you get after
running the snapshot.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128770684.895595.285420@.g44g2000cwa.googlegr oups.com...
We have 3 databases, one publisher and two subscriber.
Merge-Replication, constantly replicated.
Because of unknown circumstances, the replication process failed for
over 14 days, and the publication had a retention time of 14 days.
I wasnt detected earlier, and now is the great question:
HOW CAN I MERGE MY 3 DATABASES TOGETHER?
Is there a possibility to tell the subscribion, that they are NOT
obsolete?
To create a new snapshot won't work, i get a lot of errors afterwards.
To remove the subscriptions and setup the subscriptions anew, will
procede a well-running merge-replication, but with different data.
What about turning back the system time for a few days?
I would be very glad if you have an idea.
|||Andreas - you're quite right. To get this to work, you'll have to select
which one is the master in terms of updates to the same record on both
sites. If the updates are partitioned, then you can use this tool more
easily, but if there are a lot of conflicts then there is a big problem to
sort out, and only you can decide what is appropriate ie who wins these
conflicts, or in other words, which node is the master.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hillary,
thank you for your answer, but I don=B4t understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?
|||Unfortunately there is no master. Each of the 3 databases have the same
priority.
I cannot set anything aside.
What about my question:[vbcol=seagreen]
Do you think there is a chance to get a system which synchronizes the
data, without marking the subscibers as "obsolete"?
|||What do you think about this scenario:
1) Deleting the subscriptions
2) New pull Merge Subscriptions => Replication will run again, but with
different data
3) Restore the subscibers databases, state before deleting the
subscriptions
Do you think this will work to synchronize the databases?
If this works, maybe the changes from the publisher were not
transmittet to the subscriters yet, and I have to backup the subscribes
database and restore it as the subscribers database.
MAY THIS WORK?
|||Andreas,
this sounds feasible. You'll probably also need to change the value of the
status flag in sysmergesubscriptions. Alternatively (and probably easier)
you might be able to increase the activation/retention period and change
this flag. I haven't tried either approach and don't have any expired
subscriptions to test, so please let us know how you get on.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Yes, with merge replication when you reinitialize you are prompted to upload
the changes which happened on your subscribers first.
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
"Andreas Lauffer" <alweb01@.online.de> wrote in message
news:1128924652.854174.261410@.g47g2000cwa.googlegr oups.com...
Hillary,
thank you for your answer, but I dont understand exacly what this
means for me.
I use merge-replication, not transactual replication.
What happens, if I reinitialize my databases? Will the subscribers
upload their data then?
What is so dangerous?

HELP! Non-Replicated DB thinks its a Publisher

I hosed myself, please help me somebody.

We have a development server (SQL7) where the database is a restored copy of
production (where we use replication, but not in the development
environment).

While we rebuild the server recently (so we could move off of NT onto 2000),
everything looked to be working fine.

But I recently, messed up and did a massive update filling the transaction
log of the development server. Now I can't update data in development.

I tried to do my usual:

BACKUP LOG LAMS WITH Truncate_only

only to get a message:

The log was not truncated because records at the beginning of the log are
pending replication. Ensure the Log Reader Agent is running or use
sp_repldone to mark transactions as distributed.

Did a little research into BOL and tried to call the following two procs:

exec sp_repldone
@.xactid = null ,
@.xact_seqno = null ,
@.reset = 1

exec sp_repltrans

Both of which gave me an error of :
The database is not published.

Which it isn't as we don't run replication in the development environment.

I stopped and restarted the SQL Service (as that has cleared issues in the
past), but didn't reboot the server.

How can I make the database not think It's replicated (or that its caught
up?).

I was thinking, would I be even be able to do another restore of a more
recent copy of Production?

Sincerely ...
Depressed in DelawareNever Mind, I restored development from a more recent copy of production.

Is there a command to fix a SQL 7 DB when it gets confused about
replication?

"David Rawheiser" <rawhide58@.hotmail.com> wrote in message
news:DD3Bd.1194718$Gx4.69619@.bgtnsc04-news.ops.worldnet.att.net...
>I hosed myself, please help me somebody.
> We have a development server (SQL7) where the database is a restored copy
> of production (where we use replication, but not in the development
> environment).
> While we rebuild the server recently (so we could move off of NT onto
> 2000), everything looked to be working fine.
> But I recently, messed up and did a massive update filling the transaction
> log of the development server. Now I can't update data in development.
> I tried to do my usual:
> BACKUP LOG LAMS WITH Truncate_only
> only to get a message:
> The log was not truncated because records at the beginning of the log are
> pending replication. Ensure the Log Reader Agent is running or use
> sp_repldone to mark transactions as distributed.
> Did a little research into BOL and tried to call the following two procs:
> exec sp_repldone
> @.xactid = null ,
> @.xact_seqno = null ,
> @.reset = 1
> exec sp_repltrans
> Both of which gave me an error of :
> The database is not published.
> Which it isn't as we don't run replication in the development environment.
> I stopped and restarted the SQL Service (as that has cleared issues in the
> past), but didn't reboot the server.
> How can I make the database not think It's replicated (or that its caught
> up?).
> I was thinking, would I be even be able to do another restore of a more
> recent copy of Production?
> Sincerely ...
> Depressed in Delaware

Help! INSERT Replication not working on Subscriber FOR ONE TABLE ONLY

Everything is working great except ONE table is not replicating INSERTS from
the subscriber back to the publisher. UPDATES *are* being replicating, and
INSERTS *are* being replicated for every other table! I can't figure out
what's going on.
When I first setup the subscription/publication, the table did have a "int
identity" field. This field has now been removed but INSERTS are still not
being replicated.
Can someone please help!
Thanks!
-Ryan
Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Ben,
what type of replication are you using. Assuming it is transactional, have a
look at the article properties for the problematic table, and the commands
tab. Check that the replace Insert command isn't set to 'NONE'.
HTH,
Paul Ibison
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Paul,
I'm using merge replication. What is the "commands tab"? I can't find this
in the articles properties page.
I ended up getting this thing to work by dropping the publication and
recreating it, then reinitializing my subscriber. But I'd still like to
konw why it wasn't working before. Did it have something to do with the INT
IDENTITY field? Why didn't it work after I deleted this field?
(btw, this is Ryan, my last message was sent from a coworker's computer)
Thanks, Ryan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eVpz8BbXEHA.2840@.TK2MSFTNGP11.phx.gbl...
> Ben,
> what type of replication are you using. Assuming it is transactional, have
a
> look at the article properties for the problematic table, and the commands
> tab. Check that the replace Insert command isn't set to 'NONE'.
> HTH,
> Paul Ibison
>
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison
|||Ryan,
I don't know what was wrong previously. As you are using merge, I would have
checked that the merge trigger was firing and that the record in
msmerge_contents was being inserted. Then there are dummy updates and the
use of profiler to track what's happening. Anyway, if this crops up again
please report back and we can investigate it then.
Regards,
Paul Ibison

HELP! full text catalogs disapearing!

I have one publisher that pushes out a database to two subscribers, each with
Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
column full text indexed. The publisher is full text indexed so that QA can
test the data before replication happens.
my problem is that after the replication finishes sync'ing up the
subscribers, My tables are no longer full text enabled and my catalogue
disappears.
Can someone tell me how to avoid this? It takes about 5 ours to drop and
rebuild the catalog.
Thank you in advance!
Carl,
Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
Replication. The one-time snapshot of your table does not re-create the FT
Catalog parameter for the FT-enable table and once the table is created on
your subscriber, you will need to manually re-create the FT Catalog and set
Change Tracking with Update Index in Background and this will run a Full
Population (if the FT Catalog is un-populated).
Could you provide more details on how your subscribers tables are setup?
Thanks,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> I have one publisher that pushes out a database to two subscribers, each
with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||I am using transactional replication to push the changes from my publisher to
the subscribers. Here is the schema for one subscriber/publisher table:
CREATE TABLE defdba (
UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
DEFAULT (0),
NAME varchar(256) NULL ,
DBAID int NULL ,
JUDGEMENTID int NULL ,
MERLINNAME varchar(300) NULL ,
ADDDATE varchar(8) NOT NULL ,
msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
(
UID
) ON PRIMARY
)
I had set up the FTI to check for changes in the background, and then update
itself when replication push the data changes accross to the subscriber. From
what I am hearing you say, after I replicate the changes over, I must rebuild
the catalog from scratch each time. Is that correct?
"John Kane" wrote:

> Carl,
> Unfortunately SQL Server 2000 FT Catalogs are not directly supported with
> Replication. The one-time snapshot of your table does not re-create the FT
> Catalog parameter for the FT-enable table and once the table is created on
> your subscriber, you will need to manually re-create the FT Catalog and set
> Change Tracking with Update Index in Background and this will run a Full
> Population (if the FT Catalog is un-populated).
> Could you provide more details on how your subscribers tables are setup?
> Thanks,
> John
>
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
> with
> can
>
>
|||Carl,
No, it's the initial snapshot that is the problem as when the table schema
is created the on the subscribers, the FT Catalogs parameters (FT Catalog
name, FT-enabled columns) are not created and Change Tracking and Update
Index in background is not defined. Assuming that you do the snapshot only
once you will have to re-create this metadata only once, but when ever you
schedule another snapshot, you will have to re-do the FT Catalog metadata.
This may be the source of why your FT Catalogs are disappearing!
Furthermore, depending upon the amount (number of rows) and frequency
(batch/second or batch/hour), you should be able to use "Change Tracking"
and "Update Index in Background" on the Push Subscribers.
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:ECE0559F-C45A-4F14-A84F-9F5753BD1B1F@.microsoft.com...
> I am using transactional replication to push the changes from my publisher
to
> the subscribers. Here is the schema for one subscriber/publisher table:
> CREATE TABLE defdba (
> UID bigint NOT NULL CONSTRAINT MSrepl_synctran_identity_default_1525580473
> DEFAULT (0),
> NAME varchar(256) NULL ,
> DBAID int NULL ,
> JUDGEMENTID int NULL ,
> MERLINNAME varchar(300) NULL ,
> ADDDATE varchar(8) NOT NULL ,
> msrepl_tran_version uniqueidentifier NOT NULL CONSTRAINT
> DF__defdba__msrepl_t__5C37ACAD DEFAULT (newid()),
> CONSTRAINT PK_defdba PRIMARY KEY CLUSTERED
> (
> UID
> ) ON PRIMARY
> )
> I had set up the FTI to check for changes in the background, and then
update
> itself when replication push the data changes accross to the subscriber.
From
> what I am hearing you say, after I replicate the changes over, I must
rebuild[vbcol=seagreen]
> the catalog from scratch each time. Is that correct?
> "John Kane" wrote:
with[vbcol=seagreen]
FT[vbcol=seagreen]
on[vbcol=seagreen]
set[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]
|||I think your problem is with the way replication modifies tables for
replication.
It looks like you are complaining about the dropping of the full text
indexing on your publisher, correct? I think this is a consequence of using
queued updating which does modify both the publisher and the subscriber
tables.
I am curious as to why you are using queued? You might be able to get away
with using pure bi-directional transactional replication, depending on
certain factors.
Can you tell me exactly what you are trying to do, where do most of your
updates happen and how many subscribers you have? Also have you implemented
any partitioning scheme to avoid conflicts?
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>I have one publisher that pushes out a database to two subscribers, each
>with
> Win 2003 server and SQL 2000 sp3. This Db has two tables who each have one
> column full text indexed. The publisher is full text indexed so that QA
> can
> test the data before replication happens.
> my problem is that after the replication finishes sync'ing up the
> subscribers, My tables are no longer full text enabled and my catalogue
> disappears.
> Can someone tell me how to avoid this? It takes about 5 ours to drop and
> rebuild the catalog.
> Thank you in advance!
|||Hilary, thank you for your help!!!
My publisher server is where I process incoming raw files to update my
delivery servers (subscribers). There are six tables, two of which have one
column (MerlinName) that are FTI-enabled. Each table has between 4-6 million
rows in them.
I have Transactional Replication running once a week (The Publisher is only
updated 1-3 times a week.), and I rebuild the catalog three hours later in a
sql agent job. I rebuild that catalog each time because that only takes about
5 hours, where incremental rebuild can take up to 12 hours. I can run the sql
agent job seperately and it works fine.
Here is the sql agent job code:
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Rebuild'
EXEC sp_fulltext_catalog @.ftcat = 'db_cat', @.action = 'Start_Full'
When I come back the next day, the Subscribers data is sync'ed, but all FTI
configurations are gone. There is a catalog, but it is empty.
I dont understand how a transactional replication, which is just adding rows
to a table, can remove the FTI configurations.
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Were you able to find out anything about this?
"Hilary Cotter" wrote:

> I think your problem is with the way replication modifies tables for
> replication.
> It looks like you are complaining about the dropping of the full text
> indexing on your publisher, correct? I think this is a consequence of using
> queued updating which does modify both the publisher and the subscriber
> tables.
> I am curious as to why you are using queued? You might be able to get away
> with using pure bi-directional transactional replication, depending on
> certain factors.
> Can you tell me exactly what you are trying to do, where do most of your
> updates happen and how many subscribers you have? Also have you implemented
> any partitioning scheme to avoid conflicts?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
> "Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
> news:B94E5A44-0594-4378-A272-0E8280935B3F@.microsoft.com...
>
>
|||Carl,
As I said in my initial posting SQL Server 2000 FT Catalogs are not directly
supported with Replication. So, the replication of textual data & objects
(views, functions etc) containing full-text predicates such as CONTAINS* or
FREETEXT* during the initial snapshot is not supported. However, a
workaround is to manually create and maintain a post-snapshot script that
contains the FTS code as well as the referenced full-text indexes should be
placed in a post-snapshot script instead of being published as articles. You
can setup a SQLServerAgent job step to do this automatically and for some
T-SQL script examples , see KB article 240867 (Q240867) "INF: How to Move,
Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867
Regards,
John
"Carl Henthorn" <CarlHenthorn@.discussions.microsoft.com> wrote in message
news:2CC14822-F27F-44ED-9046-5C72714A8D79@.microsoft.com...[vbcol=seagreen]
> Were you able to find out anything about this?
> "Hilary Cotter" wrote:
using[vbcol=seagreen]
away[vbcol=seagreen]
implemented[vbcol=seagreen]
message[vbcol=seagreen]
each[vbcol=seagreen]
one[vbcol=seagreen]
QA[vbcol=seagreen]
catalogue[vbcol=seagreen]
and[vbcol=seagreen]