none
Transactional Replication

    Question

  • Hi,

    We are using transactional replication on SQL 2005 std (On Windows Server 2003)

    Due to some reasons replication is not working from past 4 days today just i checked and created new snapshot.

    After that my CPU utilization is went to 100% , Then i completely stopped replication job but no use. Then i restarted sqlservice but still cpu is 100%.

    Is this correct when creating new snapshot CPU goes by 100%?

    We having publication and distribution on one server and subscription on another server


    Saturday, July 20, 2013 4:56 AM

Answers

  • At that movement  SQLSERVR.exe only Taking 100% nothing else.

    Already the replication is running from past 3 Years.

    I observed that repldata folder lost sharing. Immediately i enabled sharing with necessary permissions and created new snapshot.

    Still SQLSERVR.exe is showing 100%.

    there is a one use Sqluser which is using for access all webserver (configured in config file)

    if i disabled that user, the CPU utilization is coming down to normal, at that movement if we enable that user, immediately the CPU utilization is going up to 100%.

    Before doing disabling this user, we have disabled SQL Agent.


    • Edited by S.Vijay Kumar Monday, July 22, 2013 1:40 PM modified
    • Marked as answer by S.Vijay Kumar Wednesday, August 21, 2013 11:09 AM
    Monday, July 22, 2013 1:36 PM

All replies

  • Hi S.Vijay,

    Transactional replication starts with a snapshot of the publication database objects and data. When the initial snapshot is taken, the data changes and schema modifications of the Publisher are usually delivered to the Subscriber. 

    If we create a new snapshot, these two processes can peg the CPU at 100%:
    svchost.exe - 60-80% CPU
    inetinfo.exe - 20-40% CPU

    In addition, there are few possibilities that cause high CPU utilization, such as application, SQL setting, I/O, network. So we need to find which processes are using the most CPU or check the snapshot to see what they are doing.

    Thanks,
    Sofiya Li



    Sofiya Li
    TechNet Community Support

    Monday, July 22, 2013 3:37 AM
  • The fact that when you stop the replication agents and you still have very high cpu indicates to me that this problem is not related to replication. Use this query to determine what process is causing the high cpu.

    SELECT TOP 10 st.text
                   ,st.dbid
                   ,st.objectid
                   ,qs.total_worker_time
                   ,qs.last_worker_time
                   ,qp.query_plan
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
    CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
    ORDER BY qs.total_worker_time DESC


    From

    http://www.sql-server-performance.com/2011/high-cpu-utilization-sql-server/


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, July 22, 2013 1:35 PM
  • At that movement  SQLSERVR.exe only Taking 100% nothing else.

    Already the replication is running from past 3 Years.

    I observed that repldata folder lost sharing. Immediately i enabled sharing with necessary permissions and created new snapshot.

    Still SQLSERVR.exe is showing 100%.

    there is a one use Sqluser which is using for access all webserver (configured in config file)

    if i disabled that user, the CPU utilization is coming down to normal, at that movement if we enable that user, immediately the CPU utilization is going up to 100%.

    Before doing disabling this user, we have disabled SQL Agent.


    • Edited by S.Vijay Kumar Monday, July 22, 2013 1:40 PM modified
    • Marked as answer by S.Vijay Kumar Wednesday, August 21, 2013 11:09 AM
    Monday, July 22, 2013 1:36 PM