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
>
>.
>

can't delete a row?

I have transaction replication setup between publisher server and subscriber
server.
I tried to delete a row from publisher's table,
but it looks like it's running forever.
delete from sym_type where type_id=1
And I don't know what to troubleshoot. but if I do an insert, then it's
working. i also see the row being replicated to subscriber when I do an
insert.
insert into sym_type values (-3, 'test', null)
scraching my head hard. replication monitor show no errors.
kevin
Kevin,
sounds like a contention issue - you could use sp_who2 and look for blocking
(use dbcc inputbuffer to see the cause of the block).
If not that, then try running select * from sym_type (nolock) where
type_id=1 to see how many rows there are - it might be a big delete?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||no... there is only 10 rows in the table.
and I inserted a row type_id = 1 to test replication, which it works
fine.
So I tried to delete the same row to rollback the change, but then it failed
to delete the row.
I think it maybe has to do with replication.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OgpkHnn1FHA.3568@.TK2MSFTNGP15.phx.gbl...
> Kevin,
> sounds like a contention issue - you could use sp_who2 and look for
> blocking (use dbcc inputbuffer to see the cause of the block).
> If not that, then try running select * from sym_type (nolock) where
> type_id=1 to see how many rows there are - it might be a big delete?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin - what about the blocking results from sp_who2 - see any?
Paul Ibison
|||yes, sp_who2 shows BlockBy - spid 60.
Command delete.
interesting huh? why can't I delete it?
I even did dbcc checktable, nothing wrong.
so i go to spid 60
it's command "delete from sym_type where type_id =1"
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OGrJB6n1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> Kevin - what about the blocking results from sp_who2 - see any?
> Paul Ibison
>
|||Kevin,
strange - what's the spid/command command that is blocked by spid 60 - are
you saying it is another spid doing exactly the same command?
Paul
|||no.. there is [BlockBy] column in sp_who2 recordset,
there is value 60
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OuuDpro1FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Kevin,
> strange - what's the spid/command command that is blocked by spid 60 - are
> you saying it is another spid doing exactly the same command?
> Paul
>
|||I'm still a little confused here You have 2 processes, and one blocks the
other. If I understand properly the one which is causing the block is the
delete, and the other one is blocked by the delete. If so, I agree that this
is strange and is the opposite of what I'd have expected. I suppose you
don't have some cunning triggers at work, or cascade deletes in action? -
worth checking out. DBCC OPENTRAN might also reveal something more but I
doubt it (still, worth a try). In your case I'd kill the delete spid, then
confirm that there's no blocking at all, and no open transactions. After
that, do a corresponding select, or perhaps just run the delete again if
it's ok.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||oh... I think I do have cascade delete in action.
my primary table is sym_type, foreign key table is portfolio_position ,
symbol_id is the key on both tables.
- it's enforce relationship for replication.
- it's enforce relationship for INSERTS and DELETES
it's cascading update related fields.
it's cascading delete related fields.
That must be it. when I do INSERT statement, there is no problem.
but when I do update or delete statement, query runs forever.
The next question is, why is this happen? I'm inserting a new symbol_id
which doesn't exist in foreign table,
why would this become a problem?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNLDCN2FHA.2444@.TK2MSFTNGP10.phx.gbl...
> I'm still a little confused here You have 2 processes, and one blocks
> the other. If I understand properly the one which is causing the block is
> the delete, and the other one is blocked by the delete. If so, I agree
> that this is strange and is the opposite of what I'd have expected. I
> suppose you don't have some cunning triggers at work, or cascade deletes
> in action? - worth checking out. DBCC OPENTRAN might also reveal something
> more but I doubt it (still, worth a try). In your case I'd kill the delete
> spid, then confirm that there's no blocking at all, and no open
> transactions. After that, do a corresponding select, or perhaps just run
> the delete again if it's ok.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Kevin,
is there a self join, or a circular-relationship between the tables perhaps?
Do you see the same behaviour in the absence of replication (if this is
possible to test). Actually you could DTS the tables to a test server and
test there.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Cant create new database -> no disk space (os error 112)

Hello everybody

Had some problems yesterday with a full transaction log. Was able to solve it by following microsofts knowledge base article n 272318.

However, today I'm trying to create a new DB using the enterprise manager and get this error:

d:\databases\HERCULES_1_Data.MDF: Operating system error 112(Es steht nicht genug Speicherplatz auf dem Datentrger zur Verfgung.) encountered.

The free disk space is > 60GB. That's by far enough... I only need 1-2 GB.

Any ideas what's wrong here?

Thanks anyone in advance for some guideance.

RenaudAre you sure both the log and the data files were being created on D? How much space is left on your C drive?|||Yes, both files on D:\ but not created at all of course, because of the insufficient disk space error message.

However, on C:\ there's 1.6 GB left. But IMO this shouldn't matter and not be related to the error message, right?|||How are you trying to create this database? By a script, or by Enterprise Manager?|||I'm using the enterprise manager|||How big is your model database? It should be about 1 or 2 MB in size.|||According to the database properties, general tab, the model database is 1.25MB in size with 0.27MB of free space.|||huh... exec xp_fixeddrives tells me:

On C, 1395MB free space
On D, 1MB free space

How can I fix it?|||... and EXEC xp_cmdshell 'dir d:\' gives me back:

1.298.432 Bytes free

Opening a command line (DOS box) and entering "dir d:\" gives me back:

64.280.199.168 Bytes free

?!?!?|||FOUND IT!! There were disk quotas activated on our SBS 2003 Server. Didn't realize that before. Seems to be a default setting when creating new user accounts on Windows. Our SQL Server runs under its own windows account. Wasn't aware of this, now I know better!

Thanks a lot for your help.

Renaud|||Thank you for reporting back the fix to a problem the rest of us may very well encounter.