Partial config on a non-replicating DB?

I have an issue on one of my test servers that I'm not entirely sure
how to solve, and would appreciate any advice people have.
We are not using replication on any of our DBs, and so far as I know
this instance/DB has never been configured for replication. Currently,
Enterprise Manager is saying that it's not set up as a publisher or
subscriber, yet one of our stored procedures is throwing error 7139
("Length of text, ntext, or image data (%ld) to be replicated exceeds
configured maximum %ld.") in some cases where a text column is being
updated.
After some investigation, it turns out that of the 400 tables in the
DB, 10 of them have related syncobj_0x... views in the DB. So it
appears to me at some point in the past there may have been an aborted
attempt at setting up snapshot replication by one of my developers, or
by some package, or something.
My question now is twofold.
First, has anyone seen this kind of thing before or have any idea what
may have gotten our DB to this state? Is it the expected state of
things if replication was configured and then turned off?
Second, what do I need to do to diagnose how extensive this
partial-setup is, and how do I clean it up? Is it as simple as
dropping the syncobj views? Are there other things I should look for
in the system tables to make sure there aren't other parts incorrectly
set up?
Thanks in advance for any help.
-Aaron
This error is normally generated by the log reader, but you don't have a log
reader - which is what makes it puzzling. Its highly abnormal, and
definitely not expected. If I could identity the problem table I would back
it up, drop it, recreate it, and restore its contents in the hope that it
solved the problem. You might want to drop a dime (actually $245 I think) on
PSS and see if they can help you.
I would try to drop the sync object views - however this should have no
impact on your system.
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
<alouts@.hotmail.com> wrote in message
news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>I have an issue on one of my test servers that I'm not entirely sure
> how to solve, and would appreciate any advice people have.
> We are not using replication on any of our DBs, and so far as I know
> this instance/DB has never been configured for replication. Currently,
> Enterprise Manager is saying that it's not set up as a publisher or
> subscriber, yet one of our stored procedures is throwing error 7139
> ("Length of text, ntext, or image data (%ld) to be replicated exceeds
> configured maximum %ld.") in some cases where a text column is being
> updated.
> After some investigation, it turns out that of the 400 tables in the
> DB, 10 of them have related syncobj_0x... views in the DB. So it
> appears to me at some point in the past there may have been an aborted
> attempt at setting up snapshot replication by one of my developers, or
> by some package, or something.
> My question now is twofold.
> First, has anyone seen this kind of thing before or have any idea what
> may have gotten our DB to this state? Is it the expected state of
> things if replication was configured and then turned off?
> Second, what do I need to do to diagnose how extensive this
> partial-setup is, and how do I clean it up? Is it as simple as
> dropping the syncobj views? Are there other things I should look for
> in the system tables to make sure there aren't other parts incorrectly
> set up?
> Thanks in advance for any help.
> -Aaron
>
|||Have I seen this? More times than I can count. Is it normal? No. But it
happens often enough. If you have synchobj... in the database, then it was
most likely merge which was setup and then removed which didn't take it out
cleanly. (This normally happens when you restore a replicated database to a
server which isn't configured for publishing.) If you look at the table,
you will more than likely have 3 triggers created against the table. Those
triggers are going to reference the views you mentioned. You should also
have a rowguid column on the table.
You can have triggers, views, procs, etc. in there. sp_removedbreplication
should clean everything up. If it doesn't, then you can manually drop any
triggers, views, procedures, indexes, and columns that are related to
replication. You then need to issue an sp_dboption to turn off the
replication related database options if they aren't off already. You may
have to go as far as turning on allow updates and resetting the replinfo
column in sysobjects to 0.
Mike
Mentor
Solid Quality Learning
http://www.solidqualitylearning.com
<alouts@.hotmail.com> wrote in message
news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>I have an issue on one of my test servers that I'm not entirely sure
> how to solve, and would appreciate any advice people have.
> We are not using replication on any of our DBs, and so far as I know
> this instance/DB has never been configured for replication. Currently,
> Enterprise Manager is saying that it's not set up as a publisher or
> subscriber, yet one of our stored procedures is throwing error 7139
> ("Length of text, ntext, or image data (%ld) to be replicated exceeds
> configured maximum %ld.") in some cases where a text column is being
> updated.
> After some investigation, it turns out that of the 400 tables in the
> DB, 10 of them have related syncobj_0x... views in the DB. So it
> appears to me at some point in the past there may have been an aborted
> attempt at setting up snapshot replication by one of my developers, or
> by some package, or something.
> My question now is twofold.
> First, has anyone seen this kind of thing before or have any idea what
> may have gotten our DB to this state? Is it the expected state of
> things if replication was configured and then turned off?
> Second, what do I need to do to diagnose how extensive this
> partial-setup is, and how do I clean it up? Is it as simple as
> dropping the syncobj views? Are there other things I should look for
> in the system tables to make sure there aren't other parts incorrectly
> set up?
> Thanks in advance for any help.
> -Aaron
>
|||Sync objects are used by the bcp process while generating the snapshot for
every replication type - EXCEPT merge.Try it.
Nor are these views are not referenced in the merge replication triggers. Do
the following
sp_helptrigger MergePublishedTable
and look for the triggers, they will look like this
ins_8C282332351F48AC95B3B81552F867BA
upd_8C282332351F48AC95B3B81552F867BA
del_8C282332351F48AC95B3B81552F867BA
then issue a sp_helptext against the triggers and you will not find the sync
objects referenced in the triggers, nor the views of the form ctsv_GUID and
tsvw_GUID which the triggers themselves reference.
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
"Michael Hotek" <mike@.solidqualitylearning.com> wrote in message
news:eD7DYEpEGHA.3000@.TK2MSFTNGP14.phx.gbl...
> Have I seen this? More times than I can count. Is it normal? No. But
> it happens often enough. If you have synchobj... in the database, then it
> was most likely merge which was setup and then removed which didn't take
> it out cleanly. (This normally happens when you restore a replicated
> database to a server which isn't configured for publishing.) If you look
> at the table, you will more than likely have 3 triggers created against
> the table. Those triggers are going to reference the views you mentioned.
> You should also have a rowguid column on the table.
> You can have triggers, views, procs, etc. in there.
> sp_removedbreplication should clean everything up. If it doesn't, then
> you can manually drop any triggers, views, procedures, indexes, and
> columns that are related to replication. You then need to issue an
> sp_dboption to turn off the replication related database options if they
> aren't off already. You may have to go as far as turning on allow updates
> and resetting the replinfo column in sysobjects to 0.
> --
> Mike
> Mentor
> Solid Quality Learning
> http://www.solidqualitylearning.com
>
> <alouts@.hotmail.com> wrote in message
> news:1136509281.066817.40350@.z14g2000cwz.googlegro ups.com...
>