Can't Drop Column or Shrink tran log

I am having problem getting the transaction log of a db
that is being replicated to shrink even after performing
a backup. There is about 500 MB of data in the database
and the trans log is almost 4GB. Dbcc checkdb returned no
error but dbcc opentran returned the following message:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (305:22434:1)
DBCC execution completed. If DBCC printed error messages,
contact your system administrator.
Also on this particular db, I can't add/drop column using
EM on the published db but can add column using
sp_repladdcolumn. When I tried to drop a column using
sp_repldropcolumn, I get the following message: "ALTER
TABLE DROP COLUMN failed because 'FieldName' is currently
replicated."
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
Emma,
I remember this thread from a while back!
It seems you have some transactions (1 or more) in the log which have not
been read by a log reader. Run your log reader agent for all publications in
this database, then turn them off and make sure no-one else can modify the
data. Run sp_replcmds to see if this returns anything else. If it does then
consider running sp_repldone. This will mark the log as having been read
completely, and you'll then be able to back it up and shrink it.
First, have a look in BOL for the 2 sps above to familiarize yourself with
them. Also, make sure you have a backup before attempting this fix.
HTH,
Paul Ibison
|||Paul,
According to BOL, sp_replcmds and sp_repldone are for
transactional replication. I am running a merge
replication. Does this make any difference?
Thanks
Emma

>--Original Message--
>Emma,
>I remember this thread from a while back!
>It seems you have some transactions (1 or more) in the
log which have not
>been read by a log reader. Run your log reader agent for
all publications in
>this database, then turn them off and make sure no-one
else can modify the
>data. Run sp_replcmds to see if this returns anything
else. If it does then
>consider running sp_repldone. This will mark the log as
having been read
>completely, and you'll then be able to back it up and
shrink it.
>First, have a look in BOL for the 2 sps above to
familiarize yourself with
>them. Also, make sure you have a backup before
attempting this fix.
>HTH,
>Paul Ibison
>
>.
>
|||Emma,
Yes! I assumed it was transactional, as the output of dbcc opentran looks
like that of transactional. Have you perhaps ever set up transactional
previously then disabled it? If so, please try running sp_repldone then see
if dbcc opentran reports anything different - hopefully no transactions will
be reported. Only do this if you don't have any current transactional
publications based on this database. As a last resort (backup your databases
before trying this), you could detach the database, delete the transaction
log, then reattach the database and a new empty log should be created for
you (I have had cause to do this previously and it worked).
HTH,
Paul Ibison
|||Paul,
Thanks for all your help. I have tried everything and
nothing is working. sp_repldone tells me that the
database is not published. I could not detach the
database because it is being replicated. I even stopped
the SQL services and deleted the log file, but this
generated an error when I restarted the database so I had
to restore the file. I disabled publication on the
database and tried to delete a column, and it tells me
that I can't delete because the table is being
replicated. Is my database corrupt? How can I get around
these problems? The two things I am trying to do is drop
a column from a published table and shrink my log file.
Any suggestions as to what I can do?
Thanks
Emma

>--Original Message--
>Emma,
>Yes! I assumed it was transactional, as the output of
dbcc opentran looks
>like that of transactional. Have you perhaps ever set up
transactional
>previously then disabled it? If so, please try running
sp_repldone then see
>if dbcc opentran reports anything different - hopefully
no transactions will
>be reported. Only do this if you don't have any current
transactional
>publications based on this database. As a last resort
(backup your databases
>before trying this), you could detach the database,
delete the transaction
>log, then reattach the database and a new empty log
should be created for
>you (I have had cause to do this previously and it
worked).
>HTH,
>Paul Ibison
>
>.
>
|||Emma,
try running
exec sp_dboption - to see the current settings
exec sp_dboption 'pubs','published',false
exec sp_dboption 'pubs','merge publish',false
This should allow you to detach the database and remove the log.
For the table, if it is still a problem, there is a stored procedure to do
this called sp_MSunmarkreplinfo which takes a tablename as a parameter.
Alternatively, setting replinfo to 0 in sysobjects for the particular table
should do it. Finally, running sp_removedbreplication can be used to remove
all traces of replication in the database, but obviously must only be done
if this database is not also configured as a publisher.
Regards,
Paul Ibison
|||Paul,
Thanks again for your help. I will try what you suggested
and hope for the best. The db is also configured as a
publisher and here is the result of sp_dboption.
ANSI null default
ANSI nulls
ANSI padding
ANSI warnings
arithabort
auto create statistics
auto update statistics
autoclose
autoshrink
concat null yields null
cursor close on commit
db chaining
dbo use only
default to local cursor
merge publish
numeric roundabort
offline
published
quoted identifier
read only
recursive triggers
select into/bulkcopy
single user
subscribed
torn page detection
trunc. log on chkpt.

>--Original Message--
>Emma,
>try running
>exec sp_dboption - to see the current settings
>exec sp_dboption 'pubs','published',false
>exec sp_dboption 'pubs','merge publish',false
>This should allow you to detach the database and remove
the log.
>For the table, if it is still a problem, there is a
stored procedure to do
>this called sp_MSunmarkreplinfo which takes a tablename
as a parameter.
>Alternatively, setting replinfo to 0 in sysobjects for
the particular table
>should do it. Finally, running sp_removedbreplication
can be used to remove
>all traces of replication in the database, but obviously
must only be done
>if this database is not also configured as a publisher.
>Regards,
>Paul Ibison
>
>.
>