how to check which tsql make increasing the log file size

Question

Post reply

how to check which tsql make increasing the log file size

ib3ans

Old Hand

Points: 327
More actions
June 7, 2020 at 12:15 pm

#3761165
Hello,
We set database log file size to 10GB, however database log file size is increased to 30 GB within one day (weekend day). So we would like to see which user or tsql is making increase log file size to very fast. Is it possible to see which transaction or sql statement is increasing the database log file to very fast?
Tran. Log Backup every 15 min(s)
Tran. Log Backup size 500kb (avg)
Regards,
- This topic was modified 3 years, 10 months ago by ib3ans.

Viewing 5 posts - 1 through 4 (of 4 total)

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

pietlinden SSC Guru Points: 63428 More actions · Answer 1

https://www.sqlshack.com/get-details-of-sql-server-database-growth-and-shrink-events/

Sergiy SSC Guru Points: 110206 More actions · Answer 2

If it happens on weekend only, then it's most likely an index maintenance job.

or a massive update on a "main" table.

_____________
Code for TallyGenerator

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

I have to agree with Sergiy. I'll also add that, if it is he index maintenance job, it's usually because of the "Death by Deframentation" horror that goes by the name of REORGANIZE. It's not the tame little low resource usage kitty that most people think it is and will frequently use much more log file than REBUILD will. I avoid the use or REORGANIZE as much as possible. The only time I use it is when I need to compact LOBs after a large number of deletions and I've had to use it up to 10 times in a row for it to finally do the compaction as complete as I believe it should on the first run but doesn't.

--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: 1003863 More actions · Answer 4

p.s. If you're doing any index maintenance because of logical fragmentation on any index that has a "0" Fill Factor, you're only making fragmentation worse and, in fact, perpetuating it. You need to figure out the insert and update patterns of such indexes and take the correct actions to fix them (and reducing the Fill Factor is frequently NOT the correct answer). For example, if you have an "ever-increasing-keyed" Clustered Index that fragmented often, reducing the Fill Factor is a complete waste of time. You need to find out where the "ExpAnsive Updates" are and fix them or take to only doing rebuilds when page density drops below something like 70-80%.

Index maintenance isn't as simple as reorganizing between 5 and 30% and rebuilding above 30%. To me, it's not a "Best Practice" and, since BOL says it's recommended only as a starting point, I have no idea how most of the world has erroneously adopted it as a "Best Practice" because it doesn't even come close to being one.

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