SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


find out what transaction caused the log file to become full yesterday or day before yesterday


find out what transaction caused the log file to become full yesterday or day before yesterday

Author
Message
coolchaitu
coolchaitu
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4341 Visits: 1386
  • Good Morning Experts,
  • An application job has been failing repeatedly with below error from last 3 days. Is there a way to find out what transaction caused the log file to become full yesterday or day before yesterday
  • failed with the following error: "The transactionlog for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.

  • Lynn Pettis
    Lynn Pettis
    SSC Guru
    SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

    Group: General Forum Members
    Points: 222575 Visits: 40378
    Have you looked at all you error logs? What do they say, if anything.
    On another note, since we can't see what you see, not much we can do to help. Your question is about as helpful as telling an automobile mechanic over the phone that your car is broke and asking what could be wrong?

    Cool
    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    coolchaitu
    coolchaitu
    SSCarpal Tunnel
    SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

    Group: General Forum Members
    Points: 4341 Visits: 1386
    Lynn Pettis - Wednesday, December 6, 2017 9:47 AM
    Have you looked at all you error logs? What do they say, if anything.
    On another note, since we can't see what you see, not much we can do to help. Your question is about as helpful as telling an automobile mechanic over the phone that your car is broke and asking what could be wrong?

    The error log has the following:
    SQL Server has encountered occurences of I/O requests taking longer than 15 seconds to complete on file g;|templog.ldf

    Lynn Pettis
    Lynn Pettis
    SSC Guru
    SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)SSC Guru (222K reputation)

    Group: General Forum Members
    Points: 222575 Visits: 40378
    Maybe I am blind and deaf, but I still don't know what to tell you. There really isn't enough information for me to even take a shot in the dark.
    Maybe someone else has an idea that can help you.

    Cool
    Lynn Pettis

    For better assistance in answering your questions, click here
    For tips to get better help with Performance Problems, click here
    For Running Totals and its variations, click here or when working with partitioned tables
    For more about Tally Tables, click here
    For more about Cross Tabs and Pivots, click here and here
    Managing Transaction Logs

    SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
    Phil Parkin
    Phil Parkin
    SSC Guru
    SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)SSC Guru (127K reputation)

    Group: General Forum Members
    Points: 127481 Visits: 22541
    Use DBCC OPENTRAN to look for open transactions.


    Help us to help you. For better, quicker and more-focused answers to your questions, consider following the advice in this link.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    Sue_H
    Sue_H
    SSC-Dedicated
    SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

    Group: General Forum Members
    Points: 33176 Visits: 9441
    coolchaitu - Wednesday, December 6, 2017 10:33 AM

    The error log has the following:
    SQL Server has encountered occurences of I/O requests taking longer than 15 seconds to complete on file g;|templog.ldf


    That's really a different error - could be indirectly related but it could just be from the file growing.
    You can see if or when a file grows in the default trace but it's not going to tell you what transaction.

    So for your first question, no.
    For the error you posted, take a look at this post to understand that error:
    Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency

    Sue



    coolchaitu
    coolchaitu
    SSCarpal Tunnel
    SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

    Group: General Forum Members
    Points: 4341 Visits: 1386
    Sue_H - Wednesday, December 6, 2017 10:54 AM
    coolchaitu - Wednesday, December 6, 2017 10:33 AM

    The error log has the following:
    SQL Server has encountered occurences of I/O requests taking longer than 15 seconds to complete on file g;|templog.ldf


    That's really a different error - could be indirectly related but it could just be from the file growing.
    You can see if or when a file grows in the default trace but it's not going to tell you what transaction.

    So for your first question, no.
    For the error you posted, take a look at this post to understand that error:
    Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency

    Sue

    I see that message before the tempdb log file full in error log

    Brandie Tarvin
    Brandie Tarvin
    SSC Guru
    SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)SSC Guru (91K reputation)

    Group: General Forum Members
    Points: 91395 Visits: 9544
    coolchaitu - Wednesday, December 6, 2017 12:32 PM
    Sue_H - Wednesday, December 6, 2017 10:54 AM
    coolchaitu - Wednesday, December 6, 2017 10:33 AM

    The error log has the following:
    SQL Server has encountered occurences of I/O requests taking longer than 15 seconds to complete on file g;|templog.ldf


    That's really a different error - could be indirectly related but it could just be from the file growing.
    You can see if or when a file grows in the default trace but it's not going to tell you what transaction.

    So for your first question, no.
    For the error you posted, take a look at this post to understand that error:
    Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency

    Sue

    I see that message before the tempdb log file full in error log


    You need to look at more than just the last error / message before the error that got your attention. You need to look at several hours worth of log warnings and errors (and possibly informational messages) to understand everything the system was doing before the error. That will give you a clearer picture of what caused the problem.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
    Sue_H
    Sue_H
    SSC-Dedicated
    SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

    Group: General Forum Members
    Points: 33176 Visits: 9441
    coolchaitu - Wednesday, December 6, 2017 12:32 PM
    Sue_H - Wednesday, December 6, 2017 10:54 AM
    coolchaitu - Wednesday, December 6, 2017 10:33 AM

    The error log has the following:
    SQL Server has encountered occurences of I/O requests taking longer than 15 seconds to complete on file g;|templog.ldf


    That's really a different error - could be indirectly related but it could just be from the file growing.
    You can see if or when a file grows in the default trace but it's not going to tell you what transaction.

    So for your first question, no.
    For the error you posted, take a look at this post to understand that error:
    Troubleshooting SQL Server I/O requests taking longer than 15 seconds – I/O stalls & Disk latency

    Sue

    I see that message before the tempdb log file full in error log


    So then you should read that article and follow the steps. There are other links in that article that you will want to check as well.

    Sue



    Jeff Moden
    Jeff Moden
    SSC Guru
    SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)SSC Guru (504K reputation)

    Group: General Forum Members
    Points: 504517 Visits: 44233
    coolchaitu - Wednesday, December 6, 2017 9:40 AM
  • Good Morning Experts,
  • An application job has been failing repeatedly with below error from last 3 days. Is there a way to find out what transaction caused the log file to become full yesterday or day before yesterday
  • failed with the following error: "The transactionlog for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.


  • 1. How big did the transaction log file become?
    2. What size is the disk that file lives on?
    3. How much free space is left on the disk?

    --Jeff Moden

    RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

    Helpful Links:
    How to post code problems
    How to post performance problems
    Forum FAQs
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum








































































































































































    SQLServerCentral


    Search