SQL Server Performance

Distribution agent job failed with log file full

Discussion in 'SQL Server 2005 Replication' started by DaddyYankee, Oct 23, 2007.

  1. DaddyYankee New Member

    Hi All,
    We have a transactional replication setup for one database(A) on server X.the distribution database located on publisher server(X).Subscriber is on different machine.It is a push subscription.
    The distribution job is failing for last 4 days ,every day with some or other issue.Generally the distribution job takes 2 min to complete ,but for past 2 days it is taking more than 3 hours and then gettng failed.I tried to setup the replication again ,first I took a freshbackup of primary database from publisher,disabled log reader,snapshot,and distribution job.Restored the backup on subscriber, executed the snapshot job,and enabled the distribution job,which execute every 10 min.Some how after running for 4 hours this distribution job failed again with log_file full error for subscriber database(B).
    I truncated the log file on subscriber databasae and executed the distribution job again.But its again more than 1 hr this job is execution.
    I checked the sysprocesses table,no blocking is observed.How ever I found that the select process from distribution database is suspended.
    I check the subscription status,it says that bulk insert for smtable is going..but I am sure this time also it will fail again.
    Any ideas what is wrong,what steps should i take to trouble shoot it or hw sud i start from scratch this replication
  2. satya Moderator

    Transaction log is used heavily in Transactional & Merge replication types, so I would like to refer http://www.sql-server-performance.com/tips/replication_tuning_p1.aspx tips too take care such activities.
    SQL Server updates the transaction log as it updates rows which form part of a transaction. It a transaction is committed it also records that it was committed or rolled back.
    SQL Server uses the transaction log on a restart to find out what transactions are written to disk and which are not on data files of the database but in the log (and SQL Server will then apply them to the database), and which transactions are rolled back but written to the data files of the database.

    If you backup the log every hour and your database is in full or bulk logged recovery model you can restore the last backup and then all of log file backups and get your database back to the point in time your database was at with the last transaction log back.

    So in this case you have to take care of transaction log backup frequently to avoid that 9002 error on the log.
  3. DaddyYankee New Member

    Thanks Satya,Your response is highly appreciated.But my problem is bit different.Even after restoring from backup,reinitializing the snapshot ,and distribution ,the distribution job failed
    Any further comment
  4. satya Moderator

    Not with replication, but do you see any other errors or warnings on the server?
  5. DaddyYankee New Member

    Nope I cant see any error.Does log reader agent have to do any thing with this issue.I have disabled the log reader agent for last 12 hours.
    Will this create any problem
    IS there is a quick way by which I can build the replication from scratch without deleting the jobs
  6. DaddyYankee New Member

    After further investigation I observed that the distribution job continously running , althouh it is schedule to execute every 10 min, this job delete rows from destination table, and then do bulk insert on these tables...again it delete and do bulk insert.I am wonderin what is wrong ?

Share This Page