Large LDF Despite Frequent Backups

Question

Post reply

Large LDF Despite Frequent Backups

homebrew01

SSC Guru

Points: 55548
More actions
September 16, 2010 at 5:08 am

#226363

We all know about backing up the T-Log to control growth, but I just had a situation where a .LDF grew enormously yesterday morning during the course of an 1.5 hours or so. I take t-log backups every 15 minutes, with a full backup every night. The .trn files are quite big during the 1.5 hours event, and add up to about 35 G, about the size of the .ldf (Backups are compressed so I'm estimating).
So, in this case, would 1 long transaction cause the log to grow despite the backups ?
I'm trying to track down what was running, but I don't know what it was yet.
(For some reason, my server side trace always seems to have stopped running whenever I need to look at it :angry: )

Viewing 12 posts - 1 through 11 (of 11 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Index rebuilds?

So, in this case, would 1 long transaction cause the log to grow despite the backups ?

Yes. The log can only be truncated up til the beginning of the oldest open transaction (simplification). An open transaction keeps the log records from the point the tran started active, and active log portions cannot be marked as reusable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 2

GilaMonster (9/16/2010)
Index rebuilds?
So, in this case, would 1 long transaction cause the log to grow despite the backups ?
Yes. The log can only be truncated up til the beginning of the oldest open transaction (simplification). An open transaction keeps the log records from the point the tran started active, and active log portions cannot be marked as reusable.

No obvious jobs like rebuilds running.

Would multiple steps within a batch have the same effect: ?

insert into tableA ....

update tableA ...

insert into tableB ....

All run in the same job step (but no explicit transactions)

crazy4sql SSCoach Points: 19590 More actions · Answer 3

Would multiple steps within a batch have the same effect: ?
insert into tableA ....
update tableA ...
insert into tableB ....
All run in the same job step (but no explicit transactions)

if they are not separated with go, i mean

insert into tableA ....

go

update tableA ...

go

.

----------
Ashish

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

homebrew01 (9/16/2010)
Would multiple steps within a batch have the same effect: ?
insert into tableA ....
update tableA ...
insert into tableB ....
All run in the same job step (but no explicit transactions)

No. If there's no explicit transaction, each statement runs as its own transaction, so that above would be three transactions, one for each statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

homebrew01 SSC Guru Points: 55548 More actions · Answer 5

So, without a trace actively running at the time, I guess there's no way to find out what was running ? A tool like Apex Log reader: "can show the data and schema changes that were made, but it cannot show what activities have been performed."

Richard M. SSCertifiable Points: 7401 More actions · Answer 6

That's where a tool like Idera's SQL Diagnostic manager comes in handy, as you can go back in time and see things like active sessions running and what command it was.... I am sure there might be other tools that allow reading of the t-logs, but that's probably more complicated....

_______________________________________________________________________
For better assistance in answering your questions, click here[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Richard M. (9/16/2010)
That's where a tool like Idera's SQL Diagnostic manager comes in handy, as you can go back in time and see things like active sessions running and what command it was

I suspect it does that by having a trace running permanently.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLBuddy123 One Orange Chip Points: 28294 More actions · Answer 8

This would most possibly have occured due to a single large transaction. If you can find the time period during which this happens, you can either use a profiler or use activity monitor to find the app or the machine from which this transaction is being submitted and can go ahead from there...

Thank You,

Best Regards,

SQLBuddy

homebrew01 SSC Guru Points: 55548 More actions · Answer 9

sqlbuddy123 (9/16/2010)
This would most possibly have occured due to a single large transaction. If you can find the time period during which this happens, you can either use a profiler or use activity monitor to find the app or the machine from which this transaction is being submitted and can go ahead from there...
Thank You,
Best Regards,
SQLBuddy

I suspect it was a one time occurence, not a regular job. I've not had log file growth so extreme on this database before.

I liked Diagnostic Manager when I had it at my previous company. I have my own trace running, but it seems to stop running at random times .... usually right before I want to look at it. I've set up an alert now if it stops running.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

GilaMonster (9/16/2010)
Richard M. (9/16/2010)
That's where a tool like Idera's SQL Diagnostic manager comes in handy, as you can go back in time and see things like active sessions running and what command it was
I suspect it does that by having a trace running permanently.

In essence that is what it does. The version that I tested also had a bad effect over time on our databases. It is something to use with caution.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

homebrew01 (9/16/2010)
sqlbuddy123 (9/16/2010)
This would most possibly have occured due to a single large transaction. If you can find the time period during which this happens, you can either use a profiler or use activity monitor to find the app or the machine from which this transaction is being submitted and can go ahead from there...
Thank You,
Best Regards,
SQLBuddy
I suspect it was a one time occurence, not a regular job. I've not had log file growth so extreme on this database before.
I liked Diagnostic Manager when I had it at my previous company. I have my own trace running, but it seems to stop running at random times .... usually right before I want to look at it. I've set up an alert now if it stops running.

Have you looked into event notifications or notification services to trigger a trace when a log growth occurs?

There is also the article I wrote to help track down this info http://www.sqlservercentral.com/articles/Log+growth/69476/

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events