locked
Transaction log backup job issue... RRS feed

  • Question

  • Currently the Transaction log backup job is stopped, because its giving performance issue. How i need to fix it..
    Friday, June 20, 2014 6:39 AM

Answers

  • See log backup actually helps you to clear out transaction logs so that is good for you, you shdn't stop it. If you have stopped that and recovery model is full then you are running out of danger zone where log file will be very soon. So either change it to simple or full.

    Also, how do you know you have deadlock issues, you might have db block issues only and if so how often these locks are clearing out.

    When you had rebuild index performed on databases along with update stats?

    For deadlock you can traceon(-1, 1222), dbcc traceon(-1, 1204)  to validate  that in your sql error log file.

    Are you sure, your disk is performing at par?


    Santosh Singh

    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 7:12 AM
  • Hi,

    You are hitting arrow in bush and in this way you wont be able to find why your data update is slow.Few points

    1. Dont stop transaction log backup as already pointed its highly unlikely for Log backup job to cause deadlock. Below query will give you all deadlocks for analysis this will work in SQL 2008 and above

    http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx

    Use Master 
    SELECT
           xed.value('@timestamp', 'datetime') as Creation_Date,
           xed.query('.') AS Extend_Event
    FROM
    (
           SELECT CAST([target_data] AS XML) AS Target_Data
           FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
           ON xs.address = xt.event_session_address
           WHERE xs.name = N'system_health'
           AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
    CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
    ORDER BY Creation_Date DESC

    2. Performance issue can we wide and can have many causes you first need to find out the cause. This should be your first move. My guess would be outdated stats, fragmented index , missing index poorly written query etc.

    3. Use this link to Analyze SQL server queries

    http://technet.microsoft.com/en-us/library/ms177500(v=sql.105).aspx

    4 Below is excellent performance whitepaper which will help you drill down issue. You have to be patient and read it completely

    http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

    Read the article and I will wait for your reply to point out where was the issue.

    If you are seeing lot of blockings below would be helpful

    http://support.microsoft.com/kb/224453

    PS: I am sorry post is verbose but this is because I dont want you to miss anything for analysis


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles


    • Proposed as answer by Sofiya Li Monday, June 23, 2014 2:45 AM
    • Edited by Shanky_621MVP Monday, June 23, 2014 8:15 AM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 12:50 PM
  • How is your disk setup local\san? If SAN, please call your vendor and also collect disk IOPS data to show them.

    did you find any db blocking?, same can be found by sp_who2 too also, under blkby column, you will blocking SPID details if any.

    If business allows you then you can go for IIS reset and clearing sql server  blockings too to get relief from blocking issues.

    Check if any db maintenance plan like backup\third party application has started backup\rebuild index job\update stats jobs are executing on server and if yes then kill them as this might be business hour for you.

    Hopefully you have good free space on disk either on log file disk and log backup disk too.

    Please plan for Sp4 for sql server 2005 if you have not that part still.


    Santosh Singh

    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 8:02 AM
  • yes my disk performing very slow ,so that i'm checking all areas..it's happening 

    Ask your networking department to run diagnostics on the disks.

    Optimal disk configuration:  http://www.sqlusa.com/bestpractices/configureharddisk/

    Check Activity Monitor, SERVER & DATABASE Standard Reports for performance related clues.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012







    • Edited by Kalman Toth Friday, June 20, 2014 1:19 PM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 12:39 PM

All replies

  • What for Performance issues do you have? Which error message are you getting? Is it a dedicated SQL Server or are there other applications running in the Server?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 20, 2014 6:47 AM
  • Do you get Any Error Message. What Job History Says?


    Nag Pal MCTS/MCITP (SQL Server 2005/2008) :: Please Mark Answer/vote if it is helpful ::

    Friday, June 20, 2014 6:47 AM
  • How did you find that log backup was giving performance issues.

    Also what is the db recovery model.

    If you need point of recovery for database then please enable log - backup again.

    What kind of performance issues you are facing?

    Please share SQL server version too and OS version too.


    Santosh Singh

    Friday, June 20, 2014 6:48 AM
  • when i try retrive the data from the database its taking lot time,so that we stopped  Transaction log backup.
    Friday, June 20, 2014 6:58 AM
  • No errors,but when we trying retrieve the data its taking lot of time,and even we faceing deadlock issue..kindly suggest what i need do solve issues
    Friday, June 20, 2014 7:00 AM
  • Main problem is data  retrieving Issue ,so We r looking Transaction logs backup and any dead locks..

    sql server 2005..windows server 2005

    Let me know what are steps i need follow resolve above issues... 

     
    Friday, June 20, 2014 7:04 AM
  • Is this happening all the time or occasional one?

    Also check if any db blocking is there, by running below command.

    select open_tran, * from sysprocesses where kpid <> 0 and blocked <> 0 and open_tran <> 0 

    Also check if any backup or any maintenance jobs are executing like rebuild index, update stats.

    Check what is CPU, memory usage on server during such time.

    Are you using linked servers to use cross server join statements?

    What is server setup, like cpu, core, RAM, disks are local or SAN?


    Santosh Singh

    Friday, June 20, 2014 7:05 AM
  • A Transaction log backup won't cause deadlocks.

    Have you checked Windows EventLog + SQL Server ErrorLog, if can see any errors there?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 20, 2014 7:07 AM
  • thank you..and how check deadlocks in database..advanced Thanx
    Friday, June 20, 2014 7:09 AM
  • No..error ..
    Friday, June 20, 2014 7:10 AM
  • See log backup actually helps you to clear out transaction logs so that is good for you, you shdn't stop it. If you have stopped that and recovery model is full then you are running out of danger zone where log file will be very soon. So either change it to simple or full.

    Also, how do you know you have deadlock issues, you might have db block issues only and if so how often these locks are clearing out.

    When you had rebuild index performed on databases along with update stats?

    For deadlock you can traceon(-1, 1222), dbcc traceon(-1, 1204)  to validate  that in your sql error log file.

    Are you sure, your disk is performing at par?


    Santosh Singh

    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 7:12 AM
  • yes my disk performing very slow ,so that i'm checking all areas..it's happening 
    Friday, June 20, 2014 7:15 AM
  • How is your disk setup local\san? If SAN, please call your vendor and also collect disk IOPS data to show them.

    did you find any db blocking?, same can be found by sp_who2 too also, under blkby column, you will blocking SPID details if any.

    If business allows you then you can go for IIS reset and clearing sql server  blockings too to get relief from blocking issues.

    Check if any db maintenance plan like backup\third party application has started backup\rebuild index job\update stats jobs are executing on server and if yes then kill them as this might be business hour for you.

    Hopefully you have good free space on disk either on log file disk and log backup disk too.

    Please plan for Sp4 for sql server 2005 if you have not that part still.


    Santosh Singh

    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 8:02 AM
  • Thank you all replays..hope i will solve ...
    Friday, June 20, 2014 12:20 PM
  • yes my disk performing very slow ,so that i'm checking all areas..it's happening 

    Ask your networking department to run diagnostics on the disks.

    Optimal disk configuration:  http://www.sqlusa.com/bestpractices/configureharddisk/

    Check Activity Monitor, SERVER & DATABASE Standard Reports for performance related clues.


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012







    • Edited by Kalman Toth Friday, June 20, 2014 1:19 PM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 12:39 PM
  • Hi,

    You are hitting arrow in bush and in this way you wont be able to find why your data update is slow.Few points

    1. Dont stop transaction log backup as already pointed its highly unlikely for Log backup job to cause deadlock. Below query will give you all deadlocks for analysis this will work in SQL 2008 and above

    http://blogs.msdn.com/b/sqlserverfaq/archive/2013/04/27/an-in-depth-look-at-sql-server-memory-part-2.aspx

    Use Master 
    SELECT
           xed.value('@timestamp', 'datetime') as Creation_Date,
           xed.query('.') AS Extend_Event
    FROM
    (
           SELECT CAST([target_data] AS XML) AS Target_Data
           FROM sys.dm_xe_session_targets AS xt
           INNER JOIN sys.dm_xe_sessions AS xs
           ON xs.address = xt.event_session_address
           WHERE xs.name = N'system_health'
           AND xt.target_name = N'ring_buffer'
    ) AS XML_Data
    CROSS APPLY Target_Data.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
    ORDER BY Creation_Date DESC

    2. Performance issue can we wide and can have many causes you first need to find out the cause. This should be your first move. My guess would be outdated stats, fragmented index , missing index poorly written query etc.

    3. Use this link to Analyze SQL server queries

    http://technet.microsoft.com/en-us/library/ms177500(v=sql.105).aspx

    4 Below is excellent performance whitepaper which will help you drill down issue. You have to be patient and read it completely

    http://technet.microsoft.com/en-us/library/dd672789(v=sql.100).aspx

    Read the article and I will wait for your reply to point out where was the issue.

    If you are seeing lot of blockings below would be helpful

    http://support.microsoft.com/kb/224453

    PS: I am sorry post is verbose but this is because I dont want you to miss anything for analysis


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    My TechNet Wiki Articles


    • Proposed as answer by Sofiya Li Monday, June 23, 2014 2:45 AM
    • Edited by Shanky_621MVP Monday, June 23, 2014 8:15 AM
    • Marked as answer by Sofiya Li Friday, June 27, 2014 6:07 AM
    Friday, June 20, 2014 12:50 PM