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

  • 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'.
  • 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?

  • 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

  • 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.

  • Use DBCC OPENTRAN to look for open transactions.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • 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

  • 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

  • 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/[/url]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.

  • 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

  • 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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Wednesday, December 6, 2017 5:55 PM

    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?

    1. It became full
    2.100GB
    3.0

  • coolchaitu - Thursday, December 7, 2017 10:32 AM

    Jeff Moden - Wednesday, December 6, 2017 5:55 PM

    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?

    1. It became full
    2.100GB
    3.0

    I'll be back.  I have to do some testing on this after work.  Do you have DBMail setup on your server?

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff Moden - Thursday, December 7, 2017 12:49 PM

    coolchaitu - Thursday, December 7, 2017 10:32 AM

    Jeff Moden - Wednesday, December 6, 2017 5:55 PM

    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?

    1. It became full
    2.100GB
    3.0

    I'll be back.  I have to do some testing on this after work.  Do you have DBMail setup on your server?

    I just realized that I read the question incorrectly.  To the best of my knowledge, no, there isn't a way to determine exactly what caused rampant logfile growth in a post mortem fashion.

    Does this have anything to do with the question you previously posted at https://www.sqlservercentral.com/Forums/1908282/Log-file-R-drive-What-is-consuming-it#bm1911711 ?

    --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.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Viewing 13 posts - 1 through 12 (of 12 total)

    You must be logged in to reply to this topic. Login to reply