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

Question

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

coolchaitu

SSChampion

Points: 10334
More actions
December 6, 2017 at 9:40 am

#326738
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 SSC Guru Points: 442467 More actions · Answer 1

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?

coolchaitu SSChampion Points: 10334 More actions · Answer 2

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 SSC Guru Points: 442467 More actions · Answer 3

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.

Phil Parkin SSC Guru Points: 246956 More actions · Answer 4

Use DBCC OPENTRAN to look for open transactions.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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 SSChampion Points: 10334 More actions · Answer 6

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 SSC Guru Points: 173045 More actions · Answer 7

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.

Sue_H SSC Guru Points: 90860 More actions · Answer 8

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 SSC Guru Points: 1004307 More actions · Answer 9

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)

coolchaitu SSChampion Points: 10334 More actions · Answer 10

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

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 11

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 SSC Guru Points: 1004307 More actions · Answer 12

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)