locked
Transaction Log Shipping Agent job (LSBackupxxx) suffering delays due to resource blocking RRS feed

  • Question

  • We are trying to debug some performance issues within our sql server 2008 OLTP database. We use Transaction Log Shipping for our DR. We have recently captured blocking events using Sql Profiler and discovered that the log shipping backup jobs, which are created automatically when you configure transaction log shipping, are implicated in blocking. The jobs run on a 5 minute interval. We have multiple database instanes. The waitresource is of type FILE. The blocking can be for as long as 140 seconds. What could possibly be causing blocking during log shipping? How can a trn file be locked? Please help me understand where to look next.

     

    Here is an example blocking report:

    <blocked-process-report>

      <blocked-process>

        <process id="process7a50508" taskpriority="0" logused="10000" waitresource="FILE: 9:0" waittime="7355" schedulerid="6" kpid="10912" status="background" spid="12" sbid="0" ecid="0" priority="0" trancount="0">

          <executionStack>

            <frame line="1" sqlhandle="0x000000000000000000000000000000000000000000000000" />

          </executionStack>

          <inputbuf />

        </process>

      </blocked-process>

      <blocking-process>

        <process status="suspended" waittime="19" spid="225" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2011-09-20T11:35:01.820" lastbatchcompleted="2011-09-20T11:35:01.807" clientapp="SQL Server Log Shipping" hostname="PHX1-NH1-DBC01" hostpid="8472" loginname="VSS-ENG\greatdivide" isolationlevel="read committed (2)" xactid="0" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">

          <executionStack>

            <frame line="1" sqlhandle="0x010001008da4f71c6072566b030000000000000000000000" />

          </executionStack>

          <inputbuf>

    BACKUP LOG [NH_DATA] TO  DISK = N'I:\NH_Logs\to_iad\NH_DATA\NH_DATA_20110920113501.trn' WITH NOFORMAT, INIT, NOSKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10   </inputbuf>

        </process>

      </blocking-process>

    </blocked-process-report>

     

    Wednesday, September 21, 2011 5:20 AM

Answers

  • How many VLF the log contains? DBCC LOGINFO(dbid)

    What if  you change a job interval to 10,15 minutes still blocking?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Stephanie Lv Thursday, September 29, 2011 11:52 AM
    Wednesday, September 21, 2011 5:42 AM
  • I agree with Uri. 5mins may be too frequently, try to use 15 or 30 interval instead.
    Best Regards
    Alex Feng

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Tuesday, September 27, 2011 3:05 AM
    • Marked as answer by Stephanie Lv Thursday, September 29, 2011 11:52 AM
    Monday, September 26, 2011 11:27 AM

All replies

  • How many VLF the log contains? DBCC LOGINFO(dbid)

    What if  you change a job interval to 10,15 minutes still blocking?


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    • Marked as answer by Stephanie Lv Thursday, September 29, 2011 11:52 AM
    Wednesday, September 21, 2011 5:42 AM
  • I agree with Uri. 5mins may be too frequently, try to use 15 or 30 interval instead.
    Best Regards
    Alex Feng

    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    • Proposed as answer by Stephanie Lv Tuesday, September 27, 2011 3:05 AM
    • Marked as answer by Stephanie Lv Thursday, September 29, 2011 11:52 AM
    Monday, September 26, 2011 11:27 AM