Re: truncate log file for replication database

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi, Hilary,
Thanks too much, I managed solve the problem by using the normal way to
truncate the log file after few days.
It is strange, anyhow now everything is fine.

Thanks too much for your suggestion!
Have a good day :)!

Shermaine

"Hilary Cotter" wrote:

Kill all log reader agents you find in task manager, try to run it again.

--
http://www.zetainteractive.com - Shift Happens!

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
"xiaomei" <xiaomei@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:13705E7A-35C2-486B-B4B2-1699842487ED@xxxxxxxxxxxxxxxx
Hi, Hilary,
I encountered problem when I tried the truncate the log file the 2nd time
by
executing :

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
@numtrans = 0, @time = 0, @reset = 1

The error is:
Executed as user: Servername\clrepl. Another log reader is replicating the
database. [SQLSTATE 42000] (Error 18752).

I tried to stop the log reader from the distributor, but seems no help.
Now I am unable to truncate the log file. :(

Very appreciate for any suggestion!

Shermaine

This will work, but you should not run into this problem. The log reader
should be able to keep up with the load unless you are doing large batch
updates/inserts or deletes.

You should be able to backup the log for it to reuse itself.

--
http://www.zetainteractive.com - Shift Happens!

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
"xiaomei" <xiaomei@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6A329F4D-C17C-4710-9AA5-6BF1B2B3FD04@xxxxxxxxxxxxxxxx
Dear There,
We just start the transaction replication for the database few month
back.
Due to the heavy transaction on the database, we schedule daily log
file
trancation before the daily full db backup happen, however, we found
the
log
file could not be truncated in the normaly way we did, it give the
below
error:

The log was not truncated because records at the beginning of the log
are
pending replication. Ensure the Log Reader Agent is running or use
sp_repldone to mark transactions as distributed.

Finally I managed truncate the log file when the DB is on line and
without
stopping the replication, now I modify my scrip of truncation by add
one
more
step bf the step:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL,
@numtrans = 0, @time = 0, @reset = 1

However, I am not sure this will 100% solve the problem, so just
wondering
there must some standard way to truncate the log file for a live
replication
database.

Many Thanks for any suggustion!

Shermaine








.

Relevant Pages

  • RE: Shrink File?
    ... As my understanding of you problem, you have a database with a data file ... in the log file. ... You can truncate only nonactive portion of the transaction log. ...
    (microsoft.public.sqlserver.setup)
  • Re: Purge Interchange DTA - HELP!!!
    ... Have you set up the "Backup BizTalk Databases" script? ... BACKUP LOG BizTalkDtaDB WITH TRUNCATE ONLY ... Note that this will truncate the log file of the DB - if your backup strategy relies on it, you are in trouble and should back it up beforehand. ... My InterchangeDTA database has become so large that the ...
    (microsoft.public.biztalk.server)
  • Re: Humongous transaction LOG!!!
    ... I did try to truncate but it didn't work. ... This is strange since the size of the backup database is about 20MB. ... I don't think that the actual size of the log file is 17GB, ... you can set the database to Simple Recovery Mode and the updates to ...
    (microsoft.public.sqlserver.server)
  • Re: truncate log file for replication database
    ... Kill all log reader agents you find in task manager, ... Looking for a SQL Server replication book? ... Now I am unable to truncate the log file. ...
    (microsoft.public.sqlserver.replication)
  • Re: Shrink File?
    ... Cannot drop the database 'wlmpetprodata' because it is currently in use. ... it is normal that your log file is much larger ... > You can truncate only nonactive portion of the transaction log. ...
    (microsoft.public.sqlserver.setup)