How to find the root cause for log .ldf file increased suddenly to 500 gb

Question

Post reply

How to find the root cause for log .ldf file increased suddenly to 500 gb

naga.rohitkumar

SSCertifiable

Points: 5738
More actions
February 15, 2024 at 6:17 am

#4378964

How to find the root cause for log .ldf file increased suddenly to 500 gb we dont know what made increase in size
is there any script to find the previous transactions occurred a day before or a week before to find out what transactions ran and what dll operations occurred or what long running transactions caused issue
please some one give the proper script
in sql logs or eventviewr logs not found anything except
this error like disk space full and log space increased
Thanks
Naga.Rohitkumar

Viewing 7 posts - 1 through 6 (of 6 total)

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

You could check the default trace, but depending how busy your server is that data may have been lost by now.

Look for event 93 for log growths

92 - Data File Auto Grow

93 - Log File Auto Grow

94 - Data File Auto Shrink

95 - Log File Auto Shrink

Lowell SSC Guru Points: 323518 More actions · Answer 2

start at the basics: can we narrow it down to see if it could have been a DBA process or a Business process?

did you have a scheduled job for statistics, or index rebuilds at the time it exploded? if you had a billion row table, and you rebuild the indexes, you'll see that kind of growth that then disappears when complete or rolled back. check the run time and how long it took, as well as if it errored.

are there business jobs that are scheduled for the time the incident occurred? did the report failure/error due to the out of space issue?

same thing, see what time it started, how long it ran, and any errors.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 3

You could/should setup an Extended Event to capture anything (including code) that makes the log file grow. Of course, you'd have to shrink the log file to a reasonable size first. A quick search should reveal some good code for the EE in short order.

Also, what are your growth settings on the log file? They should NOT be based on percentage.

As a bit of a sidebar, here are some typical "fun" things that can make your log file explode...

BAD code with accidental many-to-many JOINs due to insufficient criteria, especially on single queries with many CTEs and queries with views/nested views and queries with sub-queries, etc.

The use of REORGANIZE in Index Maintenance plans.

The use of an illegal form of UPDATE for updates that have JOINs. This illegal form is when there is a join between the table being updated and another table but the table being updated is in the UPDATE clause in the code rather than in the FROM clause with only an alias in the UPDATE clause.

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

naga.rohitkumar SSCertifiable Points: 5738 More actions · Answer 4

@SSC Guru

start at the basics: can we narrow it down to see if it could have been a DBA process or a Business process? in application they said no abnormalities other than what they run daily

did you have a scheduled job for statistics, or index rebuilds at the time it exploded? if you had a billion row table, and you rebuild the indexes, you'll see that kind of growth that then disappears when complete or rolled back. check the run time and how long it took, as well as if it errored. -- no index rebuild jobs , we do this at maintenance activity in downtime only so no chances on this also check this any one created any but no

are there business jobs that are scheduled for the time the incident occurred? did the report failure/error due to the out of space issue?

same thing, see what time it started, how long it ran, and any errors. only job or error found in the log is like

error message :- Operating system error 112(There is not enough space on the disk.) encountered.

Message

One or more recovery units belonging to database 'eG01' failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

Thanks
Naga.Rohitkumar

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 5

Quick question, what are the recovery mode and backup details?

😎

Jeff Moden SSC Guru Points: 1004361 More actions · Answer 6

Like I said 😉 ...

Jeff Moden wrote:

You could/should setup an Extended Event to capture anything (including code) that makes the log file grow. Of course, you'd have to shrink the log file to a reasonable size first. A quick search should reveal some good code for the EE in short order.

https://www.brentozar.com/archive/2015/12/tracking-tempdb-growth-using-extended-events/

And, to repeat my question, which Eirikur has also asked for...