SQL Server Performance

Log Reader Scanning the Transactions

Discussion in 'ALL SQL SERVER QUESTIONS' started by Hrishi_nk, Apr 6, 2013.

  1. Hrishi_nk New Member

    Hello Experts,
    From past few days the Log reader agent of one of my databases(Simple Recover) is scanning the transaction log records and replication is getting delayed.There are hardly 5-6 tables in replication.There is a procedure which updates these tables.The procedure is very well optimized and the tables are also properly indexed.Statitics are properly updated.In order to reduce the transactions we optimized the procedure to use views/CTEs instead of Pre-populated intermediate tables.We also created a new log reader agent profile which have readbatch size property set to higher value.
    Inspite of all these efforts,it s still scanning the transaction log.And as the transactions are marked for replication,i am not able to shrink the log file which is increasing rapidly and creating new VLF s instead of reusing the log.Is there a way by which the log reader agent reads the transaction faster and delivers it to distribution and immediatly resets the mark for replication flag.Any advice would be highly appreciated.
  2. Shehap MVP, MCTS, MCITP SQL Server

    First of all before judging on replication that it is the root reason beyond log file inflation , you should run dbcc opentranon your DB having troubles to identify Cleary what kind of transactions are consuming log file because transaction replications are usually light and don’t represent a risk for transaction log file

    If you run it , you will get the process ID which you can look up it through activities monitor of SQL Server management studio to check its related transaction or you can use sp_who2 also

    Kindly try and let me know your feedback

Share This Page