Link to home
Create AccountLog in
Avatar of bibi92
bibi92Flag for France

asked on

Replication backlog help

Hello,
I have db that the recovery model is simple, and there is replication on it.
Today, one guy stop log reader agent, the log was full. I increase log file,
and then restart log reader agent and distribution agent. After that the data
has been synchronized, but log was not truncated. that means the log size
still same, remain 70% usage. I think it should be less. Can someone give me
ideal how to truncate the log without lost datas? Thanks.

Thanks

bibi
Avatar of Humpdy
Humpdy

Have you backed up the transaction log ? That will flush it.
Avatar of bibi92

ASKER

Hello,

I have done backup log dbname with truncate_only like the recover model is simple but the log is always full.
Thanks
bibi
Avatar of Anthony Perkins
Since the Recovery Model is Simple, BACKUP LOG does not apply.  You may want to execute the following a number of times to see if that clears it:
CHECKPOINT
Avatar of bibi92

ASKER

Sorry but it's possible to do backup log dbname with truncate_only if the recovery model is simple
Avatar of bibi92

ASKER

Is it possible to execute and what do this command ?
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,    @time
= 0, @reset = 1

Thanks

bibi
>>Sorry but it's possible to do backup log dbname with truncate_only if the recovery model is simple<<
I am not sure if you realize but despite the name that is not a backup and is a deprecated command that is no longer supported.  Please make sure you backup your database immediately after using.
Avatar of bibi92

ASKER

In 2008, this command does not exist any more, I shall have had to specify that the version of the instance  is 2005 and I have already run this command. Thanks
Dear!
Distribution agent is for the initial snapshot and is not used unless you have to reinitialize replication.


find if there is some replication transactions that got stuck
using:
DBCC OPENTRAN(db_name)

from BOL
When xactid is NULL, xact_seqno is NULL, and reset is 1, all replicated transactions in the log are marked as distributed. This is useful when there are replicated transactions in the transaction log that are no longer valid and you want to truncate the log, for example:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0,     @time = 0, @reset = 1
considering you are using transaction replication:

log reader : will read the log from tlog .
 
so once the tlog is read by log reader and as you said your database is in "simple" recovery model so as soon as "checkpoint" runs till that time your tlog will be free.

for safer side just check the cleanup jobs are they running.

as suggested by "Imran fast" check for open transaction. could be long running transaction is going on.

hth
Vinay

ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of bibi92

ASKER

Thanks bibi