Can't drop column

I have a couple of questions relating to a merge
replication database.
1.I was unable to add a column to a table being
replicated using EM, but succeeded using
sp_repladdcolumn. When I tried to drop another column
using sp_repldropcolumn, I get the following
message: "ALTER TABLE DROP COLUMN failed
because 'FieldName' is currently replicated.
2.If I am unable to use EM to drop/add columns,
does this mean that my db is corrupt? How can I check if
my db is corrupt and which tools can I use?
3.The transaction log for a replicated db keeps
growing. The database is about 500 MB and the transaction
log is almost 4GB. I performed a complete backup and even
tried to shrink the log manually but the size did not
change.
I will appreciate any help I can get in resolving these
problems.
Thanks
Emma
is this column a pk, or part of a pk? are there and contraints on this
column?
It is unlikely your database is corrupt. Database base corrpuption errors
normally show up when you query a page telling you a page of the table or
index is inaccessible, your database is inaccessible. To check this run dbcc
checkdb
Regarding your ever expanding database, run dbcc open tran and see if there
are any old open transactions. If so figure out what they are doing and
evaluate killing them. The consider switching to the simple recovery model
and trying to shrink the database again several times. This will cause
locking so it is best to do this off hours. After you do this run a backup,
and then switch back to the full model.
"Emma" <eeemore@.hotmail.com> wrote in message
news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
> I have a couple of questions relating to a merge
> replication database.
> 1. I was unable to add a column to a table being
> replicated using EM, but succeeded using
> sp_repladdcolumn. When I tried to drop another column
> using sp_repldropcolumn, I get the following
> message: "ALTER TABLE DROP COLUMN failed
> because 'FieldName' is currently replicated.
> 2. If I am unable to use EM to drop/add columns,
> does this mean that my db is corrupt? How can I check if
> my db is corrupt and which tools can I use?
> 3. The transaction log for a replicated db keeps
> growing. The database is about 500 MB and the transaction
> log is almost 4GB. I performed a complete backup and even
> tried to shrink the log manually but the size did not
> change.
> I will appreciate any help I can get in resolving these
> problems.
> Thanks
> Emma
>
|||Hilary,
Thanks for your response. dbcc checkdb returned no error.
dbbc opentran returned the following and I don't know
what to do with it.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>
|||Hilary,
In response to your first question, the column that I
can't drop is not a pk or part of a pk and there are no
constraints. There is a relationship between this table
and another table on another column.
Thanks
Emma

>--Original Message--
>is this column a pk, or part of a pk? are there and
contraints on this
>column?
>It is unlikely your database is corrupt. Database base
corrpuption errors
>normally show up when you query a page telling you a
page of the table or
>index is inaccessible, your database is inaccessible. To
check this run dbcc
>checkdb
>Regarding your ever expanding database, run dbcc open
tran and see if there
>are any old open transactions. If so figure out what
they are doing and
>evaluate killing them. The consider switching to the
simple recovery model
>and trying to shrink the database again several times.
This will cause
>locking so it is best to do this off hours. After you do
this run a backup,
>and then switch back to the full model.
>"Emma" <eeemore@.hotmail.com> wrote in message
>news:175b001c418c1$10cd47e0$a501280a@.phx.gbl...
if
transaction
even
>
>.
>

Can't detach database

1. Trying to detach in EM but get message 'can't detach while db being
replicated'. OK button is disabled.
2. Replication has been disabled vua Enterprise manager and
sp_removedbreplication.
3. I know that at one time a user had tried unsuccessfullt to set up
replication between this db and a SQLServer installation on her workstation.
I don't know the details of that except that she couldn't get it to work.
4. Is there anything I can do in a system table to clean out everything
related to this replication attempt. so I can detach?
Thanks.
Alan
Alan,
try using sp_dboption to reset the database status:
sp_dboption 'dbname', 'published', 'false'
sp_dboption 'dbname', 'merge publish', 'false'
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks for your reply.
I get following message after
sp_dboption 'Contacts', 'published', 'false'
Server: Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 78
Invalid object name 'syssubscriptions'.
Would that be due to fact I tried cleaning out things in EM and
sp_removedbreplication already?
No luck in detaching. EM still says 'Database being replicated - Yes'.
Thanks.
Alan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ObTG0HpxEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Alan,
> try using sp_dboption to reset the database status:
> sp_dboption 'dbname', 'published', 'false'
> sp_dboption 'dbname', 'merge publish', 'false'
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Alan,
this shouldn't be related. It looks as though things are
very wrong here. If you don't have any other
publications, I'd consider disabling publishing on this
instance and DTS out the data from the database - perhaps
a copydatabase would also work. Then try dropping the
database.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
Thanks again for your reply.
I will try your suggestions.
Fortunately, it's a test machine. I can even re-intstall SQLServer if I
have to.
If I do re-intall, do you know if there is anything in registry that might
get left over from existing problem?
Needless to say, my client will have to rope in employees who stab around at
things.
Regards,
Alan
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:59a001c4c749$2945b230$a301280a@.phx.gbl...
> Alan,
> this shouldn't be related. It looks as though things are
> very wrong here. If you don't have any other
> publications, I'd consider disabling publishing on this
> instance and DTS out the data from the database - perhaps
> a copydatabase would also work. Then try dropping the
> database.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>